Page 1 of 1
What is wrong with this simple PHP/MySQL Query?
Posted: Thu Aug 05, 2010 11:17 am
by adamjoiner
Code: Select all
$qrya = "SELECT refid FROM jos_comprofiler WHERE ID=" . $ref[0];
$refid = mysql_query($qrya); // get referring instructors id
$qry = "UPDATE jos_comprofiler SET accountbal = accountbal + 3 WHERE ID = " . $refid;
mysql_query($qry);
The UPDATE command is not updating anything. However if I change line 2 to $refid = 62; it updates just fine. I know that the 'refid' that I am searching for in the SELECT query exists, so why is this failing?
One theory I have: Is it because I am technically calling one MySQL query within another?? If so how can I get around doing this?
Thanks for all your help everyone

Re: What is wrong with this simple PHP/MySQL Query?
Posted: Thu Aug 05, 2010 11:34 am
by Faithe
You need single quotes around your entries.
So,
Code: Select all
$qrya = "SELECT refid FROM jos_comprofiler WHERE ID = '" . $ref[0] ."'";
$refid = mysql_query($qrya); // get referring instructors id
$qry = "UPDATE jos_comprofiler SET accountbal = accountbal + 3 WHERE ID = '" . $refid ."'";
mysql_query($qry);
EDIT: vvv -- That, too (;
Re: What is wrong with this simple PHP/MySQL Query?
Posted: Thu Aug 05, 2010 11:43 am
by mikosiko
Faithe wrote:$refid = mysql_query($qrya); // get referring instructors id
mysql_query returns a recordset (or resultset) ... read:
http://php.net/manual/en/function.mysql-query.php
therefore, before to use the result you must fetch whatever your query generate... read:
http://www.php.net/manual/en/function.m ... -array.php
or any of the mysql_fetch_??? variations.
Miko
Re: What is wrong with this simple PHP/MySQL Query?
Posted: Thu Aug 05, 2010 1:48 pm
by adamjoiner
Thanks a lot for the help guys. After reading your posts, I searched and found this:
http://www.plus2net.com/sql_tutorial/my ... ch_row.php
This article pretty much sums up how to fetch what I need from the array.
A couple of questions though:
1. Even if my SELECT query only contains a single cell's value, mysql_query() will still return it as an array, and I will still have to fetch the row, correct?
2. The single quotes that were suggested for me to use around my variables, is this always necessary? After looking through some of my files, I see query's formatted in all sorts of ways...
Re: What is wrong with this simple PHP/MySQL Query?
Posted: Thu Aug 05, 2010 2:52 pm
by mikosiko
1.) Yes
2.) No always... but it is a good practice to surround object names (like column names, table names) in backticks (`) to prevent failures due to the usage of mysql reserved word (current and futures), and also is good practice and you SHOULD do it always is "sanitize" your variables before to pass them to any sql instructions.... read about it here:
http://php.net/manual/en/function.mysql ... string.php
Re: What is wrong with this simple PHP/MySQL Query?
Posted: Thu Aug 05, 2010 5:12 pm
by adamjoiner
That helps a lot. Seriously, thank you both for your help.