Help with correct syntax

[wudz]

Hi

Please could someone point out my error in my syntax with the line below and an explaination for a PHP Newbie..

mysql_query("UPDATE AUCTION_retcredit SET retid="$Seller['id']",credit=$qtity,date=$ENDS_DATE,auction="$Auction['id']",under='y' WHERE id = ".$Seller['id']);

Cheers in anticipation

John

 

[wudz]

Hi,
Sorry about the last post, gone about the requirement all wrong...so please ignore the last post..

Cheers

john

 

[jpadie]

the problem, anyway, was that you did not enquote the values.

i'd really counsel that you started your php life with the PEAR db class or some similar abstraction layer. it's a much better discipline and handles all the quotes etc for you.

 

[wudz]

Hi jpadie,
Many thanks for your response...GREAT Forum...
This is what I am after doing is INSERT not update, I get a syntax error 'check your version of SQL' or words to that effect,,,sometimes get Parse error on first line..
Regarding the last post where should the enqoute's go and have I a similar problem below?
If you would be good enough to show what the script corrected it would be appreciated.


$sellerid = $Seller['id'];

$auctionid = $Auction['id'];

$auctionend = $ends_string;

$query = "INSERT AUCTION_retcredit SET retid = $sellerid,credit = $qtity,date = $auctionend,auction = $auctionid, under = 'y'";
$res = mysql_query($query);
if(!$res) {
MySQLError($query);
exit;
}

Cheers

John

Pear db.....you got me there, is it a compiler or similar!
Sorry for being so thick, as I am a Flash graphics chap and all this is new to me..juuuusssst learning..

 

[jpadie]

to fix your code replace the line as follows:

$query = "INSERT AUCTION_retcredit SET retid = '$sellerid',credit = '$qtity',`date` = '$auctionend',auction = '$auctionid', under = 'y'";

you needed to enquote each variable. you were also using a reserved word (Date) as a column name - this is a generally bad idea! if you must do it then you can use backticks (`) to tell mysql that it's a column name rather than a function.

PEAR is a repository of classes that people can use under the GPL licence for php. basically a bunch of pre-written very high quality scripts that you can leverage to your heart's desire. A fantastic community service!

PEAR's db class is an abstraction layer meaning that you can write code using this class and by changing only one line, have it run on any database you like.

rather than worry about enquoting etc PEAR db lets you say:

$query = "Select * from table where id=? and name=?";
$params = array("1", "Justin");
$db->query($query, $params);
//or
$rows = $db->getAll($query,$params); //my personal favourite for the most useful function of all!

 

[wudz]

Hi japadie,

Many thanks for the corrected script and the informative reply most appreciated.
I learnt BASIC many many moons ago and forgot most, but understand some similarities and logic, just mainly the damed syntax and being very long in the tooth now takes longer to absorb.
OK about PEAR db I will look into it.

Cheers again and no doubt I will be posting again...

John

 

[wudz]

Hi jpadie,

Please would you tell me the reason that I get a Parse error on the line below. I have placed the array variables directly in the line rather than equal a single variable..just to tidy up the script..
Now what have I missed in your explanation...should I just go back and play with Flash...Hi

$query = "INSERT AUCTION_retcredit SET retid = '$Seller['id']',credit = '$qtity',retdate = '$ends_string',auction = '$Auction['id']', under = 'y'";
$res = mysql_query($query);
if(!$res) {
MySQLError($query);
exit;
}
I have also altered the date for reasons that you pointed out..this field has been changed on the sql..

I leave it to your expert eye and knowledge.

Cheers

John

 

[jpadie]

hi there

the reason is that you are mixing quotes. take for example

SET retid = '$Seller['id']'

here you are closing the quotes after the first square bracket and then reopening them for the last.

the correct syntax for Insert is "Insert INTO table" as well.

there is a good explanation of quoting on the php.net site.

generally, if you are going to use array elements in a query, i recommend leaving the string for each variable and concatenating the array value.

I also recommend where practical to space the query out and format it logically to aid debugging. e.g.

$query = "
		INSERT 
			INTO AUCTION_retcredit 
			SET 
				retid = '".$Seller['id']."',
				credit = '$qtity',
				retdate = '$ends_string',
				auction = '".$Auction['id']."', 
				under = 'y'
		";

Lastly I am assuming that you are already cleaning the incoming variables to prevent bad characters and/or sql injection from getting into your database? is trimming the variables and escaping them? if not i would suggest something like this as a first step before assigning your variables:

foreach ($_POST as &$val):
	if (!get_magic_quotes_gpc()):
	   $val = mysql_escape_string($val); 
	endif;
	$val = trim($val);
endforeach;

this iterates through each incoming post variable and trims the white space (etc) from each end. it also tests to see whether you have magic_quotes switched on. if you don't then it also escapes the string ready for use with a mysql string.
the "&" notation means that instead of taking a copy of the array it is operating on the original array itself.

hth
Justin

 

[wudz]

Hi Justin,
That is a great explaination and things are now falling into whats left of my grey matter. Still a lot I will have to read upon, but you have answered other problems that I did not fully understand..THANK YOU.

It is the confimatiom that the data is vaild that I will have also to get my head round as I would like the site to be bomb proof as poss.

Will get cracking again tonight see how it all goes.

Cheers

John

 

[jpadie]

right - along with pear db i recommend looking at html_quickform (also part of the pear library). there is a very good tutorial for it at

http://www.thelinuxconsultancy.co.uk.

it basically works like this:

require_once "HTML/QuickForm.php";
$form = new HTML_QuickForm('form1', 'post', $_SERVER['PHP_SELF'],"",NULL,TRUE);  // this creates the form
$form->addElement('hidden', 'formid', $formid);
$form->addElement("text", "textfield1", $label);	//this puts a text field onto the form
$buttons[] = &HTML_QuickForm::createElement
    ('submit', 'btnSubmit', 'Save', $attribs);
$buttons[] = &HTML_QuickForm::createElement
('reset', 'btnReset', 'Reset', $attribs);		
$form->addGroup($buttons, null, null, ' '); //this adds the buttons to the form in a special way so that they appear next to each other.
$form->addRule("textfield1", "$label is required", "required","",'client'); //this adds a client-side javascript rule making the user enter something
$form->addRule($fieldname, "$label is required", "required");	//this adds a server-side javascript rule making the user enter something

//now the form is constructed
//time to test whether everything is valid
if ($form->validate()):
  $form->process("validform");
else:
  $form->display();
endif;

function validform($values)
{
 echo "the form was valid".  the form values were <pre>";
 print_r($values);
 echo "</pre>";
}

from the above, hopefully you can see the enormous value that this pre-defined class can bring to form-based websites. setting up validation rules is as easy as adding a line. the code is extremely flexible and, i think, amongst the best of the generators out there. I am in the process of designing a complete CRUD/web application front-end for html_quickform and db as i find them so useful!
the learning curve, i promise, is very shallow. maybe a half-day if you're only trying to learn how to use the class. a day to get to grips with how the guts work.

 

[wudz]

Great Forum, thanks lads for coming down to my level and giving excellent advice to an old timer..GREAT

Many thanks and no doubt other posts will be flying over cyber space..

Cheers

John