assigning variables to sql query result

Question

Amateur here, just practicing some sql.

I am trying to assign php variables to my sql query results, and I thought I saw it work in this fashion, but it doesn't seem to be working for me:

$query1 = mysql_query("SELECT * FROM markers WHERE id = '$markerid'");
$result = mysql_query($query1);
$markerData = mysql_fetch_array($result, MYSQL_ASSOC);

$name = $markerData['name'];
$description = $markerData['description'];
$directions = $markerData['directions'];
$lat = $markerData['lat'];
$lng = $markerData['lng'];
$type = $markerData['type'];
$addedby = $markerData['addedby'];

Is there some rule I am blatantly disregarding?

Additionally, I know sql is deprecated/unsafe, just trying to learn the basics here.

Sincere thanks for any help.

Well if you are in the learning stage then straight way jump to prepared statement with PDO — Abhik Chakraborty, Nov 14 '14 at 07:11

score 5 · Accepted Answer · edited May 23 '17 at 11:49

You should feed a query statement into the mysql_query function, not feed it with another mysql query:

$sql = "SELECT * FROM markers WHERE id = '$markerid'";
$result = mysql_query($sql);

Obligatory Note:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

It doesn't matter if you're still in the starting stage. Since you already know that API is deprecated and your queries are usafe, why not just start good habits.

This is by way of PDO with prepared statements:

$db = new PDO('mysql:host=localhost;dbname=DB_NAME', 'username', 'password');
$sql = 'SELECT * FROM markers WHERE id = :markerid';
$select = $db->prepare($sql);
$select->bindParam(':markerid', $markerid, PDO::PARAM_INT);
$select->execute();

$markerData = $select->fetch(PDO::FETCH_ASSOC);

$name = $markerData['name'];
$description = $markerData['description'];
$directions = $markerData['directions'];
$lat = $markerData['lat'];
$lng = $markerData['lng'];
$type = $markerData['type'];
$addedby = $markerData['addedby'];

Thanks so much. Really. Learning on my own it has been difficult to get side by side comparisons as such, as well as guidance like I should give PDO a try from the outset. It's easy to get incorrect notions such as it all builds off mySQL. The code works too. — ambe5950, Nov 14 '14 at 08:16
@ambe5950 the good thing about the tutorial link above this answer is that it gives you the side-by-side comparison from mysql to PDO, which is easier to follow. you get to see its counterpart. i urge you to read it. its very helpful. im glad this helped — Kevin, Nov 14 '14 at 08:19

score 0 · Answer 2 · answered Nov 14 '14 at 07:12

You can loop trough your results like this:

while ($row = mysql_fetch_assoc($result)) {
    echo $row["name"];
    echo $row["description"];
    echo $row["directions"];
    echo $row["lat"];
    echo $row["lng"];
    echo $row["type"];
    echo $row["addedby"];
}

And your query should take a sql statement like this:

$sql = "SELECT * FROM markers WHERE id = '$markerid'";
$result = mysql_query($sql);

score 0 · Answer 3 · answered Nov 17 '20 at 16:35

0

You can use query like this.

SELECT * FROM "markers" "t" WHERE "id" = ''.$markerid.'';

answered Nov 17 '20 at 16:35

Hasini

21
1

1

Could you elaborate on your answer explaining why this will work? – Skami Nov 17 '20 at 17:04
This case specially used in php. We can assign variable like this format. \''.$markerid.'\' – Hasini Nov 19 '20 at 10:26

assigning variables to sql query result

3 Answers3