Solved

Retrieve ID from MySQL Database in PHP

Posted on 2011-09-08
31
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 8
  • +1
31 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36503202
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:Richard Quadling
ID: 36503239
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
ID: 36503246
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
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 

Author Comment

by:edhasted
ID: 36503263
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:Richard Quadling
ID: 36503317
What output do you get from
print_r($row);

Open in new window

0
 

Author Comment

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

Expert Comment

by:hielo
ID: 36503803
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
ID: 36503814
>>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
ID: 36504278
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
ID: 36504598
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
ID: 36504858
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
ID: 36505037
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
ID: 36505842
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
ID: 36505921
>>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
 

Author Comment

by:edhasted
ID: 36508445
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:Richard Quadling
ID: 36509076
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
ID: 36510954
>>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
ID: 36511720
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
ID: 36511912
>>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:Richard Quadling
ID: 36511934
@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
ID: 36512513
>>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 110

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 36516541
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
ID: 36521157
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:Richard Quadling
Richard Quadling earned 250 total points
ID: 36521619
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
ID: 36521648
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 110

Expert Comment

by:Ray Paseur
ID: 36521733
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:Richard Quadling
ID: 36521760
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 110

Expert Comment

by:Ray Paseur
ID: 36521786
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:Richard Quadling
ID: 36521830
@Ray - Yes, I often forget that.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

717 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