Retrieve ID from MySQL Database in PHP

Maybe case sensitivity?

Try ...

echo $row['id'];

Select allOpen in new window

or at least ...

print_r($row);

Select allOpen in new window

The default return type for mysql_fetch_array() is to have both numeric and associative indices.

So, 3 columns, named ID, Name and Age would be returned in an array with keys of 0, 'ID', 1, 'Name', 2 and 'Age' (the order may not be right).

If you want just the column names, then add MYSQL_ASSOC as the second parameter to mysql_fetch_array(), or use mysql_fetch_assoc().

mysql_fetch_row() is the equivalent of using MYSQL_NUM as the second parameter.

See http://www.php.net/manual/en/function.mysql-fetch-array.php,  http://www.php.net/manual/en/function.mysql-fetch-assoc.php and  http://www.php.net/manual/en/function.mysql-fetch-row.php.

You can also return an instance of an object by using mysq_fetch_object($result, $classname, $constructor_params). See  http://www.php.net/manual/en/function.mysql-fetch-object.php

It is upper case and <?php echo  $row['Username'] ?>
does return the correct Username. Hence my reason for asking the question.
Do I need to convert the ID/Int in soem way???

Ed

Could you - I didn't select that yet it came up at the bottom of the screen. I spent ages trying to find the PHP area and ended up putting it in SQL Servers. Apologies.

What output do you get from

print_r($row);

Select allOpen in new window

WIll try when I get home, allow 2 hours. Ed

What query are you using?  If you do NOT have:
SELECT Username,Email FROM Person

then only the fields listed will be available. You need:

SELECT ID, Username,Email FROM table

OR if you actually need all the columns:
SELECT * FROM Person

PS: Email and Person is just an example. You should use your actual field and table names

>>and if you do a select * the ID # is definitely there.
If you are trying to retrieve the id just after executing an INSERT query, then use:
$id=mysql_insert_id();

IMMEDIATELY after you execute your INSERT statement.

Good and fair questions. In no particulare order.

The query is:
$ses_sql=mysql_query("select * from admin where username='$user_check' ");

Chnaging "ID" to "id" crashes the program as in:
$login_id= $row['ID'];

print_r($row); gives:
Array ( [0] => 34 [ID] => 34 [1] => Edward [Username] => Edward [2] => password [Password] => password )

try:

$ses_sql=mysql_query("select * from admin where username='$user_check' ");
while( $row = mysql_fetch_assoc($ses_sql) )
{
 echo (int)$row['ID'];
}

Select allOpen in new window

That outputs nothing.
I feel I have done something monumentally but understandably stupid, but what is it?
The ID filed auto increments as it should...

OK, then try:

$sql="select * from `admin` where `username`='{$user_check}'";
$ses_sql=mysql_query($sql) or die('Unable to execute <br/>'.htmlentities($sql,ENT_QUOTES) .'<br>'.mysql_error());

if( 0==mysql_num_rows($ses_sql) )
{
  echo 'No results found';
}
else
{
  while( $row = mysql_fetch_assoc($ses_sql) )
  {
    echo (int)$row['ID'];
  }
}

what do you get?

Select allOpen in new window

Hmm, it barfs at some point in that code. Unformtunately it doesn't tell me the offending line.

Is this syntax kosher?
echo (int)$row['ID'];

>>Is this syntax kosher?
>>echo (int)$row['ID'];
There's nothing wrong with that line.  Look at your php log file.

Also, try putting this at the top of your file:

error_reporting(E_ALL);

This appears to work.

1. I removed the * and went for the explicit fields (paranoia)
2. Removed the "-'" around the field names, so

$sql="select IS,Username from `admin` where `Username`='{$user_check}'";

became

$sql="select ID, Username from admin where Username='{$user_check}'";

It worked previously just on the username but removing the quotes made it work for the ID, why???

Can you try using ...

var_dump($row);

Select allOpen in new window

inside the while loop.

This will dump both the keys and the values and tell you what types they are.

I see the print_r() shows ID as a key, so I'm really lost at the moment as to why this isn't working.

Is the code you are presenting here complete? Or are you taking our suggestions and merging them into a bigger application?

Can you try a vastly reduced script?


A couple of other things.

1 - Turn on ALL error reporting and show the errors inline.

error_reporting(-1);
ini_set('display_errors', 1);


2 - You may be able to use the mysqli extension, rather than the mysql one. It is "MySQL Improved".

Maybe not worth changing just yet, but in the long term, you'll be better supported when/if you upgrade PHP. In many cases, you will make minimal changes if you intend to use procedural calls to the mysql(i) extension. If you want to move to object-orientated programming, then mysqli comes with a set of objects you can interact with and extend to your own needs.


3 - "Hmm, it barfs at some point in that code. Unformtunately it doesn't tell me the offending line."

Nothing wrong with the line, just as Hielo says (Hi Hielo, long time no see).


4 - Is the code taking a long time to run? Are you suffering from timeouts?

If you aren't reporting the errors, I don't think you even get the timeout notifications.

Add ...

set_time_limit(0);

to the code to turn off the time limit.



Can you show us your entire code. Just in case you are doing something strange or unexpected. All the information we've provided would work out of the box under normal circumstances, so we need to try and find what isn't normal about your setup and/or code.

>>This appears to work.
>>$sql="select ID,Username from `admin` where `Username`='{$user_check}'";
Well, now you are showing Username but originally you posted username. If your db is case-sensitive, then one would work and the other wouldn't.

So, if this works:
$sql="select ID,Username from `admin` where `Username`='{$user_check}' ";

then this should work too:
$sql="select * from `admin` where `Username`='{$user_check}' ";

That was me just cleaning up the code.It appears to be the 's removal that helped.
I'm goign to rewrite this section and will repost a definitive answer, probably late in the w/e.
Many thanks for everyones' colossal input.

Ed

>>2. Removed the "-'" around the field names,...
>>....It appears to be the 's removal that helped.
An apostrophe (') is NOT the same as a "backtick" (`) character.  In mysql, the database, table, and field names are enclosed/surrounded with the backtick characters.  On the other hand, the text VALUES of the fields do use apostrophes.
 So if you had:
SELECT ... FROM 'admin' WHERE 'Username'='John'

Then that would fail, since admin and Username need the backticks:

SELECT ... FROM `admin` WHERE `Username`='John'

@Hielo, I'm only just started playing with mysql (I'm a MS SQL nearly 100% of the time, though MongoDB is starting to become meanginful).

Is the backtick 100% necessary? Or is it like [table with a space] or [column with a space] type thing?

Will ...

SELECT ... FROM admin WHERE Username = 'John'

work just fine?

>>Is the backtick 100% necessary?
No. But like other db systems, it does needed when it has spaces and/or you are using "keywords" in your table fieldNames - ex;
CREATE TABLE `Group`...
(Other VERY common problematic names that I've seen - not necessarily limited to mysql - are Date and State ).

>>SELECT ... FROM admin WHERE Username = 'John'
>>work just fine?
Yes,  that will work fine.

A. However if he had:
SELECT ... FROM 'admin' WHERE Username = 'John'

That would not work.

B. if the DB is case insensitive. His posts show that he is going back and forth between Username and username.

On another note, I typically recommend that they always use the backticks (so that they develop the habit of doing so) because sooner or later they go creating fields with keywords as names and then they find themselves struggling trying to find the problem.  If you always "quote" it with backticks, you won't run into this problem.

Regards,
Hielo

Everyone, thank you for your advice and apologies for the delay over the weekend. I'll be getting hold of the book next.

I wrote a simple test and it returned the following results:

SELECT ID,Username FROM admin WHERE Username='Edward' and Password='password' - WORKED
SELECT ID,username FROM admin WHERE Username='Edward' and Password='password' - WORKED
SELECT ID,username FROM 'admin' WHERE Username='Edward' and Password='password' - DID NOT WORK

The field name appears not to be case sensitive, HOWEVER as stated earlier it doesn't accept the ' around the table name. Which is odd as I can use that format with the on-line, web based access system for this database/table and it does.

Once again thanks to everyone for their considered advice and apologies for my naivety. Nailed it now.

From a simple question came a lot of help, especially in how to get PHP to throw up error messages, of which I knew little.

Ed

Thanks for the points.  From the parts about username and password, it appears you may be trying PHP client authentication.  If so, this article might help.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2391-PHP-login-logout-and-easy-access-control.html

Continuing from Ray's last comment, there is a community built secure login system at http://code.google.com/p/loginsystem-rd/

Login system to prevent XSS, SQL Injection and CSRF

Read mine first - it's the "easy" version and it shows the overall design pattern.  Then read the Google code - it's the industrial-strength version

:-)

~Ray

@Ray - Yes, I often forget that.