Solved

Retrieve ID from MySQL Database in PHP

Posted on 2011-09-08
31
353 Views
Last Modified: 2013-12-13
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.
0
Comment
Question by:edhasted
  • 10
  • 8
  • 8
  • +1
31 Comments
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
Maybe case sensitivity?

Try ...

echo $row['id'];

Open in new window


or at least ...

print_r($row);

Open in new window

0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
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
 

Author Comment

by:edhasted
Comment Utility
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
 

Author Comment

by:edhasted
Comment Utility
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
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
What output do you get from
print_r($row);

Open in new window

0
 

Author Comment

by:edhasted
Comment Utility
WIll try when I get home, allow 2 hours. Ed
0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>>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
 

Author Comment

by:edhasted
Comment Utility
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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
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
 

Author Comment

by:edhasted
Comment Utility
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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
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
 

Author Comment

by:edhasted
Comment Utility
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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>>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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:edhasted
Comment Utility
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
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>>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
 

Author Comment

by:edhasted
Comment Utility
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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>>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
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
@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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>>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
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
Comment Utility
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
 

Author Comment

by:edhasted
Comment Utility
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
 
LVL 40

Assisted Solution

by:RQuadling
RQuadling earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:edhasted
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
@Ray - Yes, I often forget that.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
This article discusses how to create an extensible mechanism for linked drop downs.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now