How to I retrieve data from a mysql table cell and put it into a variable in php?

Question

I am creating a function that will retrieve an id number from a table with multiple columns, put it in a variable which i want to use to send as a "claims number" in an email to a claimant and also echo on a confirmation screen. Here's what I have so far.

$sql = "SELECT id FROM warranty_claim where lname=$lname";
$result = mysql_query($sql);
$claim_id = $result;

Your code is vulnerable to SQL injection; also mysql_* suite of functions are deprecated. Consider using mysqli or PDO - this SO article can assist you further: http://stackoverflow.com/questions/1457131/php-pdo-prepared-statements — Dogoferis, Apr 22 '13 at 14:26
mysql_query returns a result handle. you need to fetch a row from that handle, which will contain the per-row data. — Marc B, Apr 22 '13 at 14:26
I'm vulnerable? I don't know much about that, how would I protect it? — Scott Morrison, Apr 22 '13 at 18:13

score 1 · Answer 1 · answered Apr 22 '13 at 14:25

1

$result = mysql_fetch_array(mysql_query($sql));

$claim_id = $result['id'];

answered Apr 22 '13 at 14:25

Ian Brindley

2,197
1
19
28

The shortest and the most simplistic approach. This definitely deserves an upvote. Don't ignore other comments that recommend you switch to PDO and MySQLi though. – Dzhuneyt Apr 22 '13 at 14:30
@ColorWP.com I figured there was enough rants about PDO and MySQLi about the place that someone clearly new to database programming didn't need an ear bashing :) Yet... :) – Ian Brindley Apr 22 '13 at 14:34

score 1 · Answer 2 · answered Apr 22 '13 at 14:26

Supposing you are sure that you will receive 1 row.

$sql = "SELECT id FROM warranty_claim where lname=$lname";
$result = mysql_query($sql);
$c = mysql_fetch_assoc($result);
$claim_id = $c['lname'];

NB*: And get ready for a lecture. Someone is going to tell you that mysql is depracated. Start using MySQLi or PDO (recommended).

score 0 · Answer 3 · answered Apr 22 '13 at 14:27

Try :

$sql = "SELECT id FROM warranty_claim where lname=$lname";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
$claim_id = $row[0];

or

$sql = "SELECT id FROM warranty_claim where lname=$lname";
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $claim_id = $row['id'];
}

score 0 · Answer 4 · answered Apr 22 '13 at 14:29

$row = mysql_fetch_array($result);

$claim_id = $row['id'];

please note that you should also check for errors (e.g. if($result === FALSE)). Also, there are other ways to fetch data - I recommend looking at mysql_fetch_array, mysql_fetch_row, mysql_fetch_assoc and mysql_fetch_object

Also, don't rely on the statement always returning exactly one row - make sure that the result is as expected: mysql_num_rows

Daanvn · Answer 5 · 2013-04-22T14:39:31.140

-1

Give this a try:

$sql        = "SELECT id FROM warranty_claim where lname='".$lname."'";
$result     = mysql_query($sql);
$objResult  = mysql_fetch_array($result);  
$claim_id   = $objResult['id'];

edited Apr 22 '13 at 14:39

answered Apr 22 '13 at 14:31

Daanvn

1,254
6
27
42

$result won't allow access to information in the resource. One of the mysql result functions need to be used. – Ian Brindley Apr 22 '13 at 14:36
@ScottMorrison Great I could help! Would you mind giving my answer an upvote since you cant accept it yet? – Daanvn Apr 22 '13 at 19:10
I just tried but it says it requires a 5 reputation. Not sure what to do now. – Scott Morrison Apr 23 '13 at 12:25

How to I retrieve data from a mysql table cell and put it into a variable in php?

5 Answers5