Problem displaying one column of one row from a database with PHP.

[Wayne Pierce]

I have a small script with PHP that queries a MySQL database to pull
out one row, where I want to be able to access each of the columns
separately. I have tried several different variations and am able to
get the entire row to print, but when I attempt to access the
individual columns I get an error. Here is what I have so far:

if (isset($_POST['memberNo'])):
$link = mysql_connect(' ...','...','... ');
mysql_select_db ("...");

//Perform a test query
$query = "SELECT * FROM users WHERE 'id' = " . $_POST['memberNo'];
$result = mysql_query($qu ery) or die(mysql_error ());

$line = mysql_fetch_ass oc($result);
print $line;

//Close connection
mysql_close($li nk);

The point of this code is to retrieve the user information to validate
the login information sent in the previous form. I do not get an
error with this code, but it also does not print anything. I know
there is an entry in the database that matches data sent in the
$_POST['memberNo'] variable, it is the only entry in the database.

Changing the 'print $line;' to 'print $line["id"];' does not display
anyting either.

Does anyone know where I am making the mistake? There should only be
one row returned and I want to be able to access the columns in that
row as an array.

Thanks for any information,

Wayne

[Tom Thackrey]

Re: Problem displaying one column of one row from a database with PHP.


On 30-Oct-2003, wayne@mishre.co m (Wayne Pierce) wrote:
[color=blue]
> I have a small script with PHP that queries a MySQL database to pull
> out one row, where I want to be able to access each of the columns
> separately. I have tried several different variations and am able to
> get the entire row to print, but when I attempt to access the
> individual columns I get an error. Here is what I have so far:
>
> if (isset($_POST['memberNo'])):
> $link = mysql_connect(' ...','...','... ');
> mysql_select_db ("...");
>
> //Perform a test query
> $query = "SELECT * FROM users WHERE 'id' = " . $_POST['memberNo'];
> $result = mysql_query($qu ery) or die(mysql_error ());
>
> $line = mysql_fetch_ass oc($result);
> print $line;
>
> //Close connection
> mysql_close($li nk);
>
> The point of this code is to retrieve the user information to validate
> the login information sent in the previous form. I do not get an
> error with this code, but it also does not print anything. I know
> there is an entry in the database that matches data sent in the
> $_POST['memberNo'] variable, it is the only entry in the database.
>
> Changing the 'print $line;' to 'print $line["id"];' does not display
> anyting either.
>
> Does anyone know where I am making the mistake? There should only be
> one row returned and I want to be able to access the columns in that
> row as an array.[/color]

I'm surprised your code doesn't die with on the query. You need to enclose
the field name id in back ticks not apostrophies.

You should be testing $line to see if it's null indicating no row was
returned.

print $line['id']; should work if a row was actually found.

If memberNo is not all digits and id is not defined as an int, you need to
enclose the value in apostrophies (e.g. '$_POST[memberNo]')

It's really a bad idea to insert user supplied data into a query without at
least addslashes().


--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[Andy Hassall]

Re: Problem displaying one column of one row from a database with PHP.

On 30 Oct 2003 09:01:04 -0800, wayne@mishre.co m (Wayne Pierce) wrote:
[color=blue]
>I have a small script with PHP that queries a MySQL database to pull
>out one row, where I want to be able to access each of the columns
>separately. I have tried several different variations and am able to
>get the entire row to print, but when I attempt to access the
>individual columns I get an error. Here is what I have so far:
>
>if (isset($_POST['memberNo'])):
> $link = mysql_connect(' ...','...','... ');[/color]

Never ignore the return value of mysql_query (or any other MySQL function), as
queries can fail, returning 'false'. The function mysql_error() will tell you
why.

For debugging, you can use something like:

$result = mysql_query($qu ery)
or die("Query failed: $query<br />Error: ".mysql_error() ."<br />");

For a production system, you should never display raw error messages to the end
user, and should use some sort of graceful error handling to inform the user
that the function is currently unavailable.
[color=blue]
> mysql_select_db ("...");[/color]

As above.
[color=blue]
> //Perform a test query
> $query = "SELECT * FROM users WHERE 'id' = " . $_POST['memberNo'];[/color]

That will return no rows. You're comparing the string 'id' with what's in
$_POST['memberNo']. If you want to compare with the id column, leave it
unquoted.

Also you've got a security problem, as you're open to SQL injection attacks
(unless magic_quotes_gp c is on, which is generally more trouble than it's
worth).

Use addslashes() on the data; for example:

$query = sprintf("SELECT * FROM users WHERE id = '%s'",
addslashes($_PO ST['memberNo']);
[color=blue]
> $result = mysql_query($qu ery) or die(mysql_error ());[/color]

OK - error checking here!
[color=blue]
> $line = mysql_fetch_ass oc($result);
> print $line;
>
>The point of this code is to retrieve the user information to validate
>the login information sent in the previous form. I do not get an
>error with this code, but it also does not print anything. I know
>there is an entry in the database that matches data sent in the
>$_POST['memberNo'] variable, it is the only entry in the database.
>
>Changing the 'print $line;' to 'print $line["id"];' does not display
>anyting either.
>
>Does anyone know where I am making the mistake? There should only be
>one row returned and I want to be able to access the columns in that
>row as an array.[/color]

Your query matched no rows, so you get Boolean false back. When you try and
print that, you get nothing.

If you had got a row, you'd get Array(), since you're trying to print an
array. But you got the right idea to try $line['id'].

if ($line = mysql_fetch_ass oc($result))
print $line['id']
else
print 'No rows returned.';

If all you want is the id column, then only select that column, rather than
select * - only select what you're going to use.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)