How to retrieve a single row

[SimonIoa]

i want to retrieve a single row from my database
i use this and i get nothing

$session_country = mysql_result("SELECT name FROM countries WHERE country_id='15'");

i found this online but still the same

$session_country = mysql_result(mysql_result("SELECT name FROM countries WHERE country_id='15'"), 0);

[cereal]

The function mysql_result() returns one of the rows of the result set, so if you get a total of 10 rows, you can decide to get one of those:

$query = mysql_query('SELECT * FROM tablename');
$row   = mysql_result($query, 2);

the count starts from 0.

To get a single row, instead, use the limit statement:

$query = mysql_query('SELECT * FROM tablename WHERE country_id = 15 LIMIT 1');

Docs: https://dev.mysql.com/doc/refman/5.6/en/select.html

[SimonIoa]

So Sorry for that i meant

$session_country = mysql_result(mysql_query("SELECT name FROM countries WHERE country_id='15'"), 0);

[SimonIoa]

I dont think thats possible because the country_id is unique, i mean that there is only one (e.g 15) that corresponds to certain name (e.g. France)

I dont want to use

$query = mysql_query('SELECT name FROM tablename WHERE country_id = 15 LIMIT 1');
$row=mysql_fetch_array($query);
echo $row['name'];

i was wondering if i can do that with a single line. To set a variable that is, like this

$session_country = mysql_result("SELECT name FROM countries WHERE country_id='15' LIMIT 1");

[cereal]

Ok, if country_id is unique then you don't need the limit statement. Use mysql_fetch_row() instead of mysql_fetch_array(), but you really should not use these function anymore, these are deprecated and will be removed. With PDO you can do:

$row = $pdo->query('SELECT name FROM countries WHERE country_id = 15', PDO::FETCH_OBJ)->fetch();

echo $row->name;

[SimonIoa]

thanks cereal i use this

$row = $pdo->query('SELECT name FROM countries WHERE country_id=15', PDO::FETCH_OBJ)->fetch();
echo $row->name;

But i get a blank page. Is it possible that the WAMP server i am using locally doesnt support this? $pdo?

[cereal]

The variable $pdo, in my example, is the object that carries the connection to the database, for example:

$pdo = new PDO("mysql:dbname=DATABASE", "USERNAME", "PASSWORD");

Documentation: http://php.net/manual/en/pdo.query.php

[SimonIoa]

ok Sorry for the delay. Its the Orthodox Easter Holiday.

Ok mine is

$db = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD,DB_DATABASE) or die(mysqli_connect_error());
so i do this?

$row = $db->query('SELECT name FROM countries WHERE country_id = 15', DB::FETCH_OBJ)->fetch(); echo $row->name;

still a blank page

[cereal]

No problem & happy holidays ;)

It can be done with MySQLi but you're mixing procedural and object oriented (OOP) styles.

In OOP the connection is open like this:

$db = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);

Then you can use $db to run the methods. MySQLi hasn't defined a fetch() method, in your case you would use fetch_object():

$row = $db->query('SELECT name FROM countries WHERE country_id = 15')->fetch_object();

echo $row->name;

Also: the query() method accepts a second argument, but this is used to define the result mode, here you can find some information about these modes:

• (default behaviour) http://php.net/manual/en/mysqli.store-result.php
• http://php.net/manual/en/mysqli.use-result.php
• http://php.net/manual/en/mysqli.query.php

For a list of fetch modes, instead, check the methods defined for the mysqli_result class:

• http://php.net/manual/en/class.mysqli-result.php

[SimonIoa]

Thanks Cereal Once again.... Hope you doing better with your thing