Retrieve ID from MySQL Database in PHP

I have a table with an autoincremented field called "ID".
Why, if I use this coding approach, can't I retrieve the ID?
$row=mysql_fetch_array($result);
echo $row['ID'];

I can pull other data from the same record and if you do a select * the ID # is definitely there.
LVL 1
edhastedAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
This is a really good book - very readable with great examples.
http://www.sitepoint.com/books/phpmysql4/

Looking at the original question we find this:

$row=mysql_fetch_array($result);
echo $row['ID'];

How do you know what is in the $result variable?  If it is not a MySQL result resource, the content of $row will be meaningless.  For that reason it is a good idea to test your queries and see if they actually worked.  If they fail, you can get an indicator of the reason for failure.  For example...
// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);

// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS

Open in new window

0
 
Richard QuadlingSenior Software DeveloperCommented:
Maybe case sensitivity?

Try ...

echo $row['id'];

Open in new window


or at least ...

print_r($row);

Open in new window

0
 
Richard QuadlingSenior Software DeveloperCommented:
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
edhastedAuthor Commented:
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
0
 
edhastedAuthor Commented:
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.
0
 
Richard QuadlingSenior Software DeveloperCommented:
What output do you get from
print_r($row);

Open in new window

0
 
edhastedAuthor Commented:
WIll try when I get home, allow 2 hours. Ed
0
 
hieloCommented:
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

0
 
hieloCommented:
>>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.
0
 
edhastedAuthor Commented:
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 )

0
 
hieloCommented:
try:


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

Open in new window

0
 
edhastedAuthor Commented:
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...
0
 
hieloCommented:
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?

Open in new window

0
 
edhastedAuthor Commented:
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'];
0
 
hieloCommented:
>>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);

0
 
edhastedAuthor Commented:
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???
0
 
Richard QuadlingSenior Software DeveloperCommented:
Can you try using ...

var_dump($row);

Open 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.


0
 
hieloCommented:
>>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}' ";
0
 
edhastedAuthor Commented:
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
0
 
hieloCommented:
>>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'
0
 
Richard QuadlingSenior Software DeveloperCommented:
@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?
0
 
hieloCommented:
>>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
0
 
edhastedAuthor Commented:
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.
0
 
Richard QuadlingConnect With a Mentor Senior Software DeveloperCommented:
Please note,
single quote '

Open in new window

is not the same as
backtick `

Open in new window


Single quotes are for "strings". Backticks are for `table names`, `column names` and `database names`.

SELECT `column` FROM `database`.`table` WHERE `column` = 'string'

Open in new window


The reference you need to look at is about something called "quoted identifiers".

I suppose it is unfortunate that the word "quoted" is there.

Maybe "escaped identifiers" may have been more appropriate.

Either way, the 8.2. Schema Object Names reference covers what you are needing to know about the different ways to identify elements.

If ANSI_QUOTES are enabled, then you can use
SELECT "column" FROM "database"."table" WHERE "column" = 'string'

Open in new window


That's a double quote.

I can't see a reference allowing single quotes.

It may be that the tool you are using parses your query and magically replaces your single quotes for backticks. That would seem odd but possible. I'd say it would be a bad idea too. Allowing you to enter incorrect SQL (assuming it is parsing) and correcting it, without telling you it has done so, provides you with the wrong information to use the query outside of the tool.


0
 
edhastedAuthor Commented:
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
0
 
Ray PaseurCommented:
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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2391-PHP-login-logout-and-easy-access-control.html
0
 
Richard QuadlingSenior Software DeveloperCommented:
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
0
 
Ray PaseurCommented:
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
0
 
Richard QuadlingSenior Software DeveloperCommented:
@Ray - Yes, I often forget that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.