My problem in here (probably) is that $db->fetch_array won't show all the results from the db. I don't know what happens but I just get 1 of the 3 results, I tried many things even I changed the query a bit. Do you have any ideas why I can't get all the results in here?
It's for vBulletin 3.8 btw.
Thanks people.
if ($_REQUEST['do'] == 'showthis') {
$rel = $db->query_first("
SELECT *
FROM " . TABLE_PREFIX . "anexampletable
WHERE fromuserid OR touserid = " . $vbulletin->userinfo['userid'] . "
AND confirmstatus =1
");
if ($rel) {
$queryrel = $db->query_read("
SELECT *
FROM " . TABLE_PREFIX . "anexampletable
WHERE fromuserid OR touserid = " . $vbulletin->userinfo['userid'] . "
AND confirmstatus =1
");
while ($queryre = $db->fetch_array($queryrel)) {
if ($queryre['reltype'] == '1') {
$ty = " is something else ";
} else if ($queryre['reltype'] == '2') {
$ty = " is something ";
} else if ($queryre['reltype'] == '3') {
$ty = " is something else ";
} else if ($queryre['reltype'] == '4') {
$ty = " is something ";
} else if ($queryre['reltype'] == '5') {
$ty = " is something ";
} else if ($queryre['reltype'] == '6') {
$ty = " is something ";
} else if ($queryre['reltype'] == '7') {
$ty = " is something ";
} else if ($queryre['reltype'] == '8') {
$ty = " is something ";
} else if ($queryre['reltype'] == '9') {
$ty = " is something ";
} else if ($queryre['reltype'] == '10') {
$ty = " is something ";
} else if ($queryre['reltype'] == '11') {
$ty = " is something else ";
} else if ($queryre['reltype'] == '12') {
$ty = " is something else ";
} else if ($queryre['reltype'] == '13') {
$ty = " is something ";
} else if ($queryre['reltype'] == '14') {
$ty = " is something ";
} else {
$ty = " is default ";
}
$sender = $queryre['fromusername'];
$receiver = $queryre['tousername'];
$showit = $sender . $ty . $receiver;
}
eval('print_output("' . fetch_template('relationships') . '");');
}
}
Your query is saying:
WHERE `fromuserid` has any value
OR (touserid = $vbulletin->userinfo['userid']).
If you want the rows where either fromuserid matches the userid or touserid matches the userid, try this:
$queryrel = $db->query_read("
SELECT * FROM " . TABLE_PREFIX . "anexampletable
WHERE (fromuserid = " . $vbulletin->userinfo['userid'] . ")
OR (touserid = " . $vbulletin->userinfo['userid'] . ")
AND confirmstatus =1
");
Update:
It's hard to determine the problem without knowing the data you're working with. I've created a test that outputs the data your code is working with, you'll be able to see what the individual parts of your query are returning and then can determine where the problem lies.
Modify your file temporarily by placing this code just before the code in your example (you'll need to use the correct table name). Then edit your question and paste the output at the bottom.
echo "vBulletin User ID = " . $vbulletin->userinfo['userid'];
$test_query1 = $db->query_read("
SELECT * FROM " . TABLE_PREFIX . "anexampletable
WHERE (fromuserid = " . $vbulletin->userinfo['userid'] . ")
");
$t1_count = 0;
echo "Test From User ID Results<br />";
while ($test1_output = $db->fetch_array($test_query1)) {
$t1_count++;
echo "Test From User Result " . $t1_count . "<br />";
echo "From User ID = " . $test1_output['fromuserid'] . "<br />";
echo "To User ID = " . $test1_output['touserid'] . "<br />";
echo "Confirm Status = " . $test1_output['confirmstatus'] . "<br />";
echo "Relationship Status = " . $test1_output['reltype'] . "<br />";
}
$test_query2 = $db->query_read("
SELECT * FROM " . TABLE_PREFIX . "anexampletable
WHERE (touserid = " . $vbulletin->userinfo['userid'] . ")
");
$t2_count = 0;
echo "<br /><br />Test To User ID Results<br />";
while ($test2_output = $db->fetch_array($test_query2)) {
$t2_count++;
echo "Test To User Result " . $t2_count . "<br />";
echo "From User ID = " . $test2_output['fromuserid'] . "<br />";
echo "To User ID = " . $test2_output['touserid'] . "<br />";
echo "Confirm Status = " . $test2_output['confirmstatus'] . "<br />";
echo "Relationship Status = " . $test2_output['reltype'] . "<br />";
}
exit();
Final Code?
It appears that there were two problems:
1) The query needed to be modified:
Original:
fromuserid OR touserid = " . $vbulletin->userinfo['userid'] . "
Updated:
(fromuserid = " . $vbulletin->userinfo['userid'] . "
OR
touserid = " . $vbulletin->userinfo['userid'] . ")
Updated 07/05/2012
2) You can't loop through arrays in vb3 templates, so we'll concatenate strings.
The $showit variable being output for use in the template is a string. It's being overwritten by each successive pass through the while loop so that only the last result is sent to the template. Instead of using $showit = xxx;, use $showit .= xxx; with .=.
I've updated the last 15 lines or so of the code below.
You can look at how the forum page is generated to learn more.
Open the upload\forumdisplay.php file. The while loop that creates the list of threads starts here:
upload\forumdisplay.php(962)
while ($thread = $db->fetch_array($threads))
The output for each thread is generated using the "threadbit" template and added to the $threadbit string here:
upload\forumdisplay.php(1000)
eval('$threadbit .= "' . fetch_template('threadbit') . '";');
The "FORUMDISPLAY" template is output at the end:
upload\forumdisplay.php(1056)
eval('print_output("' . fetch_template('FORUMDISPLAY') . '");');
If you look at the FORUMDISPLAY template, you'll find that the $threadbit string is used about 1/5 from the beginning.
Try the code below and see how it works, I replaced the series of else if statements with a switch() statement. It's more efficient.
if ($_REQUEST['do'] == 'showthis') {
// Make standalone query, easy to output query string and run it directly for testing
$rel_sql = "SELECT * FROM " . TABLE_PREFIX . "anexampletable
WHERE (fromuserid = " . $vbulletin->userinfo['userid'] . "
OR touserid = " . $vbulletin->userinfo['userid'] . ")
AND confirmstatus =1";
$queryrel = $db->query_read($rel_sql);
if ($db->num_rows($queryrel))
{
while ($queryre = $db->fetch_array($queryrel))
{
switch ($queryre['reltype'])
{
case 1:
$ty = " do something 1 ";
break;
case 2:
$ty = " do something 2 ";
break;
case 3:
$ty = " do something 3 ";
break;
// Add as many cases as needed
.......
case xxx:
$ty = " do something xxx ";
break;
.......
default:
$ty = " is default ";
}
$sender = $queryre['fromusername'];
$receiver = $queryre['tousername'];
// UPDATED FROM HERE DOWN on 07/05/2012
// Add to $showit with ".=" rather than overwriting it with "=".
// Method One
// If the output is simple, try this.
// I added a line break after each entry.
$showit .= $sender . $ty . $receiver . "<br />";
OR
// Method Two
// If the output is complex.
// Create a separate template and store the output in $showit
// Remember to add the new template to the $actiontemplates array.
eval('$showit .= "' . fetch_template('showit') . '";');
}
eval('print_output("' . fetch_template('relationships') . '");');
}
}