Page 1 of 1

mysql_fetch($query) only working after command client exe

Posted: Fri Dec 04, 2009 12:57 pm
by darkwing85
Hi everyone,
I am new to php and mysql (like a lot of people on here), and I have been noting this strange behavior:

mysql_fetch() in my php code seems to only execute AFTER I manually open up the Mysql command client and do the query. Below is the pertinent php code where I post a username and pass (uname and pass) to this php file and then use them to query for information pertaining to that user.

Code: Select all

 
<head>
<?php
 
session_start();
?>
<head>
<?php
 
if (!empty($_SESSION)){
echo "Welcome ".$_SESSION['firstname'];
}
else{
 $con=mysql_connect(null,'ychou','cockroach');
if(!$con)
{
die('Could not connect: '. mysql_error()); 
}
 
mysql_select_db("music_db", $con);
 
$uname=mysql_real_escape_string($_POST["uname"],mysql_connect(null,'ychou','cockroach'));
$pass=mysql_real_escape_string($_POST["pass"],mysql_connect(null,'ychou','cockroach'));
 
$query = "SELECT firstname FROM members WHERE username= '$uname' AND userpass= '$pass'"; 
        
$result = mysql_query($query,$con);
        if (!$result) {
            die ('Query failed'. mysql_error());
            }
        if (mysql_num_rows($result) > 0) {
                $row = mysql_fetch_array($result);
            while ($row = mysql_fetch_array($result))
           {
                $_SESSION['fname'] = $row['firstname'];
                $_SESSION['lname']= $row['lastname'];
                $_SESSION['band'] = $row['band'];
        $_SESSION['bandmems']= $row['bandMembers'];
        $_SESSION['musicexp']= $row['musicexp'];
 
        $_SESSION['uname'] = $uname;
        $_SESSION['password'] = $pass;
            }
}
echo "Welcome back, ". $row['firstname'];
}
?>
The last line echo "Welcome back, ". $row['firstname']; will usually generate a "NOTICE: Undefined index firstname" if I simply execute by navigating using IE. BUT if I do the query listed above "SELECT firstname FROM members WHERE username= '$uname' AND userpass= '$pass'"; manually on the mysql command line client, and then refresh my IE explorer, the index is no longer "undefined" and I get the result:
Welcome back Tom.
(Assuming the firstname of user is Tom).
I had the Mysql client open and logged into it using the root user. I also have sql developer installed concurrently and notice on task manager that mysqld is always runnign concurrently with mysql. Could there be some problem regarding how I connect to the mysql server?

Thanks, and sorry for not being able to post the code in highlighting because I dont have an editor.

Re: mysql_fetch($query) only working after command client exe

Posted: Fri Dec 04, 2009 1:58 pm
by darkwing85
Oops scrap what I said before, I had changed the last line to echo $uname to debug and that's why it was outputting a name. It seems after some debugging that:
1) $result is not null. So the connection to mysql is working as is selection of the database and the table.
2) $row is not null, so the query for username and pass choice is done.
3) However, $row['firstname'] comes back as null. My table in the databse had information columns entered normally as:
firstname VARCHAR(25)
lastname VARCHAR(25)... etc.

What is going on? My PK is the username for this table.

Thanks so much for any help!

Re: mysql_fetch($query) only working after command client exe

Posted: Fri Dec 04, 2009 2:15 pm
by AbraCadaver
You're only looking for one user so get rid of the while() loop.

-Shawn

Re: mysql_fetch($query) only working after command client exe

Posted: Fri Dec 04, 2009 2:24 pm
by darkwing85
Now taking out the while loop I get all these notices:
Notice: Undefined index: firstname in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\sessionlogin.php on line 39

Notice: Undefined index: lastname in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\sessionlogin.php on line 40

Notice: Undefined index: band in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\sessionlogin.php on line 41

Notice: Undefined index: bandMembers in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\sessionlogin.php on line 42

Notice: Undefined index: musicexp in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\sessionlogin.php on line 43
Welcome back,

All the indices are as is in the database... so I didnt spell anything wrong.

Re: mysql_fetch($query) only working after command client exe

Posted: Fri Dec 04, 2009 2:59 pm
by AbraCadaver
I can understand all of the ones except 'firstname' because you only selected 'firstname' in your query so the others won't be there. Try chaning your query to SELECT * and then after:

Code: Select all

$row = mysql_fetch_array($result);
add

Code: Select all

var_dump($row);
-Shawn

Re: mysql_fetch($query) only working after command client exe

Posted: Fri Dec 04, 2009 3:43 pm
by darkwing85
Oh hey that last function you posted helped me realize firstly that the query result row was returned with numerical indices. I had already tried using numbers as indices before but I always did $row[0] and then by pure luck I tried $row['0'] and now it works! But its pretty ridiculous because the php manual shows you dont need to quote the numerical indices, but apparently you do. Thanks Shawn you're awesome.

Re: mysql_fetch($query) only working after command client exe

Posted: Fri Dec 04, 2009 4:12 pm
by AbraCadaver
And also mysql_fetch_array() should return both a numerically indexed array and an associative array with field names as keys. So what you have should work unless it changed in some version. What version do you have?

-Shawn

Re: mysql_fetch($query) only working after command client exe

Posted: Fri Dec 04, 2009 4:18 pm
by darkwing85
I downloaded php5 for windows but this is really weird I agree. Oh well good old open source. :lol:

Re: mysql_fetch($query) only working after command client exe

Posted: Fri Dec 04, 2009 4:45 pm
by AbraCadaver
darkwing85 wrote:I downloaded php5 for windows but this is really weird I agree. Oh well good old open source. :lol:
Please try mysql_fetch_array($result, MYSQL_ASSOC) mysql_fetch_array($result, MYSQL_BOTH) and mysql_fetch_assoc($result) and let us know what you get. I've been a regular on the PHP lists for a long while and know the internals guys and some others. This seems weird and maybe my drunken self missed something in your code, but it seems like a wierd bug. Please post your complete PHP version.

1. On a command line cd to your php dir and type 'php -v'
--or--
2. Create a file in your web dir with:

Code: Select all

<?php phpinfo(); ?>
and load it in the browser

-Shawn

Re: mysql_fetch($query) only working after command client exe

Posted: Thu Dec 10, 2009 12:10 pm
by darkwing85
Hi Shawn,
Oh it's great that you are looking into this! I have been busy with work so I have not played with my php for a week now. When I did php -v in my command line I get:

PHP 5.2.11 )cli) (built: Sep 16 2009 19:39:46)
Copyright 1997-2009 The PHP Group
Zend Engine v2.2.0 Copyright 1998-2009 Zend Technologies.

And I havent explcitly tried your new statements again, especially not the BOTH line (which is the default anyway) but I did remember trying the Assoc and Num lines before to no avail. So I don't really know what is going on. By the way if you are responsible for the PHP manual documentation, you might want to update the syntax for grabbing numerical indices because they have it wrongly as:

"while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
printf("ID: %s Name: %s", $row[0], $row[1]);
}
"

In one of their examples on the mysql_fetch_array() page. Or I guess it might be a windows thing? I'm on windows xp and use internet explorer version 7.