Running SQL Query

[GoremanX]

I'm trying to modify one of the installed MODs to display data from elsewhere in the database, but nothing I'm trying is working. I'm NOT an experienced PHP coder, nor do I know much about phpBB in general. I've been trying to get this working by re-using existing code that performs similar tasks. But of course it's not working

So far, my code looks like the following:

Code: Select all

$query_pms_count="SELECT count(u.id) FROM " . $dbpref . "uddeim AS u WHERE u.totrash=0 AND u.toread=0 AND u.toid=".$row['id'];

I've confirmed that this translates to the following query:

Code: Select all

SELECT count(u.id) FROM jos_uddeim AS u WHERE u.totrash=0 AND u.toread=0 AND u.toid=62

And when I run that query in phpmyadmin, I get exactly the result I want, which is the number of new private messages for the logged-in user in the external PM system (currently 1).

After that variable declaration, I have the following line:

Code: Select all

$pms_count = $db->sql_query($query_pms_count);

which I assume would perform the query contained in the $query_pms_count variable and store the result in $pms_count. But that doesn't seem to be the case. Am I not understanding the sql_query() function properly? I've already tried using just about every other similar function in the /includes/db/mysqli.php file, but none of them are working the way I expect them to. In all cases, $pms_count appears to remain empty.

[GoremanX]

I figured it out, I didn't realize my query returned a mysql table, not a result. I couldn't reference the variable directly since it wasn't just a single integer result. I had to pass the result to sql_rowseek() first to get the integer out of the table and make it usable as a string variable.

Live and learn...

[Sajaki]

in this case sql_fetchfield would be better.

Code: Select all

$sql="SELECT count(u.id) as pms_count FROM " . $dbpref . "uddeim AS u WHERE u.totrash=0 AND u.toread=0 AND u.toid=".$row['id'];

$result = $db->sql_query($sql);
 
$pms_count = (int) $db->sql_fetchfield('pms_count');

[GoremanX]

in this case sql_fetchfield would be better.

Code: Select all

$sql="SELECT count(u.id) as pms_count FROM " . $dbpref . "uddeim AS u WHERE u.totrash=0 AND u.toread=0 AND u.toid=".$row['id'];

$result = $db->sql_query($sql);
 
$pms_count = (int) $db->sql_fetchfield('pms_count');

Yeah I figured that out after wiki.phpbb.com was accessible again

Thanks!