Retrieving row from MySQL Database via PHP

Question

Please bear with me, I'm new here - and I'm just starting out with PHP. To be honest, this is my first project, so please be merciful. :)

    $row = mysql_fetch_array(mysql_query("SELECT message FROM data WHERE code = '". (int) $code ."'         LIMIT 1"));
    echo $row['message'];

Would this be enough to fetch the message from the database based upon a pre-defined '$code' variable? I have already successfully connected to the database.

This block of code seems to return nothing - just a blank space. :(

I would be grateful of any suggestions and help. :)

UPDATE:

Code now reads:

    <?php
    error_reporting(E_ALL);
   // Start MySQL Connection
    REMOVED FOR SECURITY
    // Check if code exists
    if(mysql_num_rows(mysql_query("SELECT code FROM data WHERE code = '$code'"))){
    echo 'Hooray, that works!';
    $row = mysql_fetch_array(mysql_query("SELECT message FROM data WHERE code = '". (int) $code     ."' LIMIT 1")) or die(mysql_error());
    echo $row['message'];
    }
    else {
    echo 'That code could not be found. Please try again!';
    }
    mysql_close();
    ?>

You need to add or die(mysql_error()); to the end of your query. — Kermit
– Kermit, Commented Nov 30, 2012 at 19:50
If you're just learning, look for tutorials that use MySQLi or PDO with prepared statements rather than the old, deprecated MySQL interface — Mark Baker
– Mark Baker, Commented Nov 30, 2012 at 19:54
@njk See above reply, also - the rest of the code is not being executed, so that part of my code is obviously defective. — user1867357
– user1867357, Commented Nov 30, 2012 at 19:57
@user1867357 Are your errors being suppressed? Add error_reporting(E_ALL); before this code. — Kermit
– Kermit, Commented Nov 30, 2012 at 19:57
@user1867357 Please delete your comment. Update your question with the error. — Kermit
– Kermit, Commented Nov 30, 2012 at 20:00

Sammitch · Accepted Answer · 2012-11-30 19:57:42Z

1

It's best not to chain functions together like this since if the query fails the fetch will also appear to fail and cause an error message that may not actually indicate what the real problem was.

Also, don't wrap quotes around integer values in your SQL queries.

if(! $rs = mysql_query("SELECT message FROM data WHERE code = ". (int) $code ." LIMIT 1") ) {
    die('query failed! ' . mysql_error());
}
$row = mysql_fetch_array($rs);
echo $row['message'];

And the standard "don't use mysql_* functions because deprecated blah blah blah"...

If you're still getting a blank response you might want to check that you're not getting 0 rows returned. Further testing would also include echoing out the query to see if it's formed properly, and running it yourself to see if it's returning the correct data.

answered Nov 30, 2012 at 19:57

Sammitch

32.5k7 gold badges57 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1867357 Over a year ago

It is now only displaying the first message in the database, regardless of the codes. :(

Sammitch Over a year ago

Well, I seem to have gone on the assumption that the code column in your database is an INTEGER datatype. If it's a CHAR/VARCHAR/etc then you will want to wrap it in single quotes. Other than that you'll need to echo the query and test.

user1867357 Over a year ago

Wrap which part in single quotes? Thanks for all your help, by the way. :)

Sammitch Over a year ago

SELECT message FROM data WHERE code = 1 LIMIT 1 if the column code is a numeric type, SELECT message FROM data WHERE code = '1' LIMIT 1 if it is a string type.

user1867357 Over a year ago

It is a VARCHAR type. I replaced SELECT message FROM data WHERE code = ". (int) $code ." LIMIT 1 with SELECT message FROM data WHERE code = '1' LIMIT 1

|

Community · Accepted Answer · 2023-11-21 15:10:29Z

Some comments:

Don't use mysql_*. It's deprecated. use either mysqli_* functions or the PDO Library
Whenever you enter a value into a query (here, $code), use either mysqli_real_escape_string or PDO's quote function to prevent SQL injection
Always check for errors.

Example using PDO:

//connect to database
$user = 'dbuser';  //mysql user name
$pass = 'dbpass';  //mysql password
$db   = 'dbname';  //name of mysql database
$dsn = 'mysql:host=localhost;dbname='.$db;
try {
    $con = new PDO($dsn, $user, $pass);
} catch (PDOException $e) {
    echo 'Could not connect to database: ' . $e->getMessage();
    die();
}

//escape code to prevent SQL injection
$code = $con->quote($code);
//prepare the SQL string
$sql = 'SELECT message FROM data WHERE code='.$code.' LIMIT 1';
//do the sql query
$res = $con->query($sql);
if(!$res) {
    echo "something wrong with the query!";
    echo $sql; //for development only; don't output SQL in live server!
    die();
}
//get result
$row = $res->fetch(PDO::FETCH_ASSOC);
//output result
print_r($row);

Collectives™ on Stack Overflow

Retrieving row from MySQL Database via PHP

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related