mysql_fetch($query) only working after command client exe

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
darkwing85
Forum Newbie
Posts: 9
Joined: Fri Dec 04, 2009 12:44 pm

mysql_fetch($query) only working after command client exe

Post 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.
darkwing85
Forum Newbie
Posts: 9
Joined: Fri Dec 04, 2009 12:44 pm

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

Post 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!
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post by AbraCadaver »

You're only looking for one user so get rid of the while() loop.

-Shawn
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
darkwing85
Forum Newbie
Posts: 9
Joined: Fri Dec 04, 2009 12:44 pm

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

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
darkwing85
Forum Newbie
Posts: 9
Joined: Fri Dec 04, 2009 12:44 pm

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

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
darkwing85
Forum Newbie
Posts: 9
Joined: Fri Dec 04, 2009 12:44 pm

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

Post by darkwing85 »

I downloaded php5 for windows but this is really weird I agree. Oh well good old open source. :lol:
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
darkwing85
Forum Newbie
Posts: 9
Joined: Fri Dec 04, 2009 12:44 pm

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

Post 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.
Post Reply