MySQL Query Question

[Mark.B]

I'm slightly noobish at this and get muddled up easily but I do eventually get there.

I'm messing about with a shoutbox addon.

I have got the following query:
$shout = $vbulletin->db->query_first( "SELECT id, idaut, name, message, time FROM mkp_urlobox ORDER BY 'id' DESC LIMIT 10");

And I thought that would pull 10 rows from the table...but it doesn't, it only pulls one, the most recent.

What really obvious thing am I missing here to make it do the last 10? I am sure I ought to know this! :)

[Lats]

query_first, by design, grabs only one result (first). Try using query_read.

[Mark.B]

query_first, by design, grabs only one result (first). Try using query_read.

Thanks.

When I change it to that, it doesn't fetch anything at all.

The whole piece of code looks like this now:

$shout = $vbulletin->db->query_read( "SELECT id, idaut, name, message, time FROM mkp_urlobox ORDER BY 'id' DESC LIMIT 10");
$name = $shout['idaut'];
$message = $shout['message'];

But although there are no errors, it's not fetching any results.

[Lats]

$shout would now be an array - try $db->fetch_array.

[Mark.B]

Still nothing. :(

Just fetching no results at all.

[Lats]

I can only suggest looking at some vB code - for example, look at lines 194 through 199 of online.php - that gives a short example of you combine query_read with fetch_array.

[Lats]

Depending on your version, those line numbers may be different - look for $moderators = $db->query_read.

[Mark.B]

I had a look through that, and also through some code I use that grabs the last 5 active threads....

The latter in particular, I have copied the syntax and format exactly and just replaced replaced the table and row info, and it *still* only fetches one.

I can only conclude that something in the table itself is preventing this from working.

The table is MKPortal code, which can be baffling enough at the best of times.

[Marco van Herwaarden]

Some basic examples of retrieving data:

vBulletin 3.0
Retrieving a single row:
$result = $DB_site->query_first("SELECT * FROM " . TABLE_PREFIX . "table WHERE rowid = 1");
$result will contain an array with all columns of the retrieved table row.

Retrieving multiple rows:
// Prepair the query
$result_set = $DB_site->query_read("SELECT * FROM " . TABLE_PREFIX . "table");
// Loop through all results:
while ($result = $DB_site->fetch_array($result_set))
do
{
// $result will contain 1 fetched row here
....process data....
}


vBulletin 3.5 and higher:
Retrieving a single row:
$result = $vbulletin->db->query_first("SELECT * FROM " . TABLE_PREFIX . "table WHERE rowid = 1");
$result will contain an array with all columns of the retrieved table row.

Retrieving multiple rows:
// Prepair the query
$result_set = $vbulletin->db->query_read("SELECT * FROM " . TABLE_PREFIX . "table");
// Loop through all results:
while ($result = $vbulletin->db->fetch_array($result_set))
do
{
// $result will contain 1 fetched row here
....process data....
}

[Mark.B]

Thanks Marco...that has sorted it for me...it wasn't looping back properly so it was only reading one row.

I am pleased to say it is now working correctly. I even managed to add a LEFT JOIN in to read the username from the USER table, I am quite pleased with myself.