Getting a specific row of data from a merged table specified by an ID

Question

I try to get data stored in SQL from specific row from a merged table

this is what I tried so far

<?php
$id = $_GET['id'];

$getdetails = mysql_query("SELECT 
scholarship.scholarshipid,
scholarship.scholarshipname, 
scholarship.shortdescription, 
scholarship.scholarshippicture,

scholarshipdetail.fulldescription,
scholarshipdetail.degreetype,
scholarshipdetail.location,
scholarshipdetail.deadline
FROM scholarship, scholarshipdetail
WHERE scholarship.scholarshipid = scholarshipdetail.scholarshipid AND scholarship.scholarshipid = $id ");

$retval = mysql_query( $getdetails, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}

?>

The id is gotten from theurl.php?id=IDNUMBER But it turns out that it could not fetch the data. How can I get a data from a row specified by the ID number in PHP?

of course you need to fetch it first. and stop using mysql functions and use mysqli or PDO with prepared statements instead. — Kevin, Nov 23 '14 at 07:01

worldofjr · Answer 1 · 2014-11-23T07:27:51.913

You've tried to perform a mysql_query on the result from another mysql_query!

Let's assume that your SQL is correct for the moment, and work on the rest of your code. Firstly you need to use MySQLi or PDO as the mysql extension is deprecated. So in MySQLi;

$mysqli = new mysqli('host', 'user', 'password', 'db'); // fill in your details

$id = $_GET['id'];
if($stmt = $mysqli->prepare("SELECT 
scholarship.scholarshipid,
scholarship.scholarshipname, 
scholarship.shortdescription, 
scholarship.scholarshippicture,
scholarshipdetail.fulldescription,
scholarshipdetail.degreetype,
scholarshipdetail.location,
scholarshipdetail.deadline
FROM scholarship, scholarshipdetail
WHERE scholarship.scholarshipid = scholarshipdetail.scholarshipid
AND scholarship.scholarshipid = ?")) {

    $stmt->bind_param('i',$id);
    $stmt->execute();
    $result = $stmt->get_result();
}
else {
    echo $mysqli->error;
}

while($row = $result->fetch_assoc()) {
    // do stuff
    // cols stored as $row['col_name'];
}

Note the ? in the prepared SQL statement where $id was. This is the placeholder for the variable which is then bound with $stmt->bind_param('i',$id); (i means integer, s would be used for a string). You then have to execute the result and get the result set before you can do anything with it.

If there is an error in your SQL then the error will be outputted to the browser, and the query won't be executed.

Hope this helps.

Getting a specific row of data from a merged table specified by an ID

1 Answers1