What is wrong with this simple PHP/MySQL Query?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
adamjoiner
Forum Newbie
Posts: 8
Joined: Sun Jul 11, 2010 11:57 pm

What is wrong with this simple PHP/MySQL Query?

Post 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 :D
Faithe
Forum Commoner
Posts: 33
Joined: Tue Jul 12, 2005 3:26 pm
Location: WA

Re: What is wrong with this simple PHP/MySQL Query?

Post 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 (;
Last edited by Faithe on Thu Aug 05, 2010 11:47 am, edited 1 time in total.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: What is wrong with this simple PHP/MySQL Query?

Post 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
adamjoiner
Forum Newbie
Posts: 8
Joined: Sun Jul 11, 2010 11:57 pm

Re: What is wrong with this simple PHP/MySQL Query?

Post 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...
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: What is wrong with this simple PHP/MySQL Query?

Post 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
adamjoiner
Forum Newbie
Posts: 8
Joined: Sun Jul 11, 2010 11:57 pm

Re: What is wrong with this simple PHP/MySQL Query?

Post by adamjoiner »

That helps a lot. Seriously, thank you both for your help.
Post Reply