RESOLVED Does this Query have a chance?

[Dax IX]

Not only am I pretty new to writing my own PHP code, I'm even newer at writing my own SQL queries and even newer at writing anything for vBulletin, if that makes sense...

I'm JUST starting a vBa CMPS page for a friend of mine's site and before I get too far into this, I'd like to know if this query has a chance of working, given that all of the tables and other variables are correct...

$menus = $db->query_read("
SELECT id, shortdescription
FROM " . TABLE_PREFIX . "menus
WHERE " . for($i = 0 ; $i < $n ; $i++){ . "(id = " . $menuids_array[$i] . ") " . if($i < ($n - 1)){ . "AND "}}
);In other words, can I include a for() function in a query?

Remember, I'm just a noob at this, so if I've done something horribly wrong in my code, I'm simply just not aware of what you can and cannot do in SQL queries yet. :o (or PHP, apparently)

Oh, and also, this is the second query on this page. Is it possible to combine two queries if one of them contains the results of the other? (See code above)

Thanks! :)


Realized I needed to change a couple things.


--------------- Added 31 Dec 2008 at 05:38 ---------------

I'm learning that I have a LOT to learn... :o

Never mind...I figured out the query I need to run, but I'm having problems with the rest of my script... :(

[Dismounted]

$menuids = implode(',', $menuids_array);
$menus = $vbulletin->db->query_read("
SELECT id, shortdescription
FROM " . TABLE_PREFIX . "menus
WHERE id IN ($menuids)
");

[Dax IX]

Thank you so much. That just shortened everything quite nicely.

Now I'm just having issues with everything else. :(

I'm desperately trying to learn PHP and vBulletin functions all at the same time in as little time as possible. Kinda messing me up here...

Sorry for being such a (n OR b)oob.

--------------- Added 1230731403 at 1230731403 ---------------

Okay, I've figured most of it out now (yes, I've been searching, not just asking ;) ), but I'm coming across a problem (My fault, I'm sure. I'm not afraid to say that I know almost nothing here).

In my database I've entered bogus "menus" and "planners".

With:
$menus_array = $vbulletin->db->fetch_array($menus);

echo print_r($menus_array);I'm only getting one row as a result. (Array ( [id] => 1 [shortdescription] => my first breakfast menu ) 1)

How do I get an array with all results? (Please don't shoot me :o )

Maybe I should mention that I changed my code a little, so the whole implode() function wasn't necessary. $menuids was already "1,2,3,4".

[Lynne]

Probably something more along these lines:
while ($menus_array = $vbulletin->db->fetch_array($menus))
{
echo print_r($menus_array);
}

[Dismounted]

This is what you want:
$menus_array = array();
while ($menu = $vbulletin->db->fetch_array($menus))
{
$menus_array[] = $menu;
}

echo '<pre>';
echo print_r($menus_array);
echo '</pre>';

[Dax IX]

Thanks. :)

I was able to get sort of the results that I want, but now the problem is that I need to have them sorted in the order that is called for in $menuids.

For example, $menuids is supposed to be "5,10,3,1,4,7", but it's returning the results in numerical order ASC.

I've tried using array_multisort(), but one of the arrays is a multi-dimensional array, and one is not, so it's not working...in other words, I've turned the data in $menuids into an array and tried sorting $menus_array with it. Nope. Didn't work...

I'm just taking stabs in the dark here. If I should post my script code, I will.

Thank you all for your assistance. :)

[Dismounted]

MySQL will automatically sort by the primary key if no order clause is specified in a query. Dirty way is below, I can't think of anything else off the top of my head.
$menuids = '5,10,3,1,4,7';
$menus = $vbulletin->db->query_read("
SELECT id, shortdescription
FROM " . TABLE_PREFIX . "menus
WHERE id IN ($menuids)
");

$menus_temp = array();
while ($menu = $vbulletin->db->fetch_array($menus))
{
$menus_temp[$menu[$id]] = $menu;
}

$menus_array = array();
$id_order = explode(',', $menuids);

foreach ($id_order AS $id)
{
$menus_array[] = $menus_temp[$id];
}

[Dax IX]

Thank you. :)

Now the problem I'm having is how to actually call the [shortdescription] from my query with this information.

Grrr...I really should know how to do this, but I'm just not getting it. :( I admit, I haven't gone through learning PHP from the beginning, but instead I just dive right in and seemingly waste people's time.

But, with this new info, how would I call the [shortdescription] for each table cell that I'm putting these in?

Here's some context:
$planner_rows = 3;
$planner_columns = 7;
$meals = array("Breakfast","Lunch","Dinner");
$days_of_week = array("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday");

if($vbulletin->userinfo['userid']){

$planners = $vbulletin->db->query_read("
SELECT *
FROM " . TABLE_PREFIX . "planners
WHERE memberid = " . $vbulletin->userinfo['userid'] . "
ORDER BY id DESC LIMIT 1
");



$planners_array = array();
$planners_array[] = $vbulletin->db->fetch_array($planners);

$menuids = $planners_array[0][menuids];

$menus = $vbulletin->db->query_read("
SELECT id, shortdescription
FROM " . TABLE_PREFIX . "menus
WHERE id IN ($menuids)
");

}

$menus_temp = array();
while ($menu = $vbulletin->db->fetch_array($menus))
{
$menus_temp[$menu[$id]] = $menu;
}

$menus_array = array();
$id_order = explode(',', $menuids);

foreach ($id_order AS $id)
{
$menus_array[] = $menus_temp[$id];
}


echo "<table style='border:none;width:1070px;'>
<tr>
<td style='border:none;width:70px'>&nbsp;</td>";
for($n = 0; $n < $planner_columns; $n++) {
echo " <td style='color:#FF0000;width:140px;border:none;paddi ng:5px;'><strong>" . $days_of_week[$n] . "</strong></td>";
}
echo " </tr>";
for($i = 0; $i < $planner_rows; $i++) {
echo " <tr>
<td style='color:#FF0000;width:70;border:none;text-align:right;padding-right:5px;'><strong>" . $meals[$i] . "</strong></td>";
$list_menu = $i;
for($ii = 0; $ii < $planner_columns; $ii++) {
echo "<td style='width:140;border:1px solid black;padding:5px;'>" . ($list_menu +1) . " " . $menu[$menus_array[$list_menu]][shortdescription];

$list_menu += $planner_rows;
echo "</td>";
}
echo "</tr>";
}
echo "</table>";
And here's the page:

http://www.whatz4dinner.com/portal.php?pageid=planner
(I know you can't see the results, but this is the format)

Thank you so much for helping me with this. :)

[Dismounted]

I don't see why you needed to results in any specific order. Replace what I posted before with this:
$menuids = '5,10,3,1,4,7';
$menus = $vbulletin->db->query_read("
SELECT id, shortdescription
FROM " . TABLE_PREFIX . "menus
WHERE id IN ($menuids)
");

$menus_array = array();
while ($menu = $vbulletin->db->fetch_array($menus))
{
$menus_array[$menu[$id]] = $menu;
}
You can call "shortdescription" like this:
$menus_array[$menuid]['shortdescription']

[Dax IX]

The reason is because of the way I have the table filled. It's a vertical loop of sorts, but I guess I could just reorder [menuids] and fill the table horizontally.

Thank you so much for your help. I'll go in and see what I can do. :)

[Dismounted]

You don't seem to be looping the $menus_array, but rather, just referencing it using a set key. So I don't know what the order of the array has to do with anything.

[Dax IX]

I'll study it a little more, but this seems to give me EXACTLY what I'm looking for:

$planner_rows = 3;
$planner_columns = 7;
$meals = array("Breakfast","Lunch","Dinner");
$days_of_week = array("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday");

if($vbulletin->userinfo['userid']){

$planners = $vbulletin->db->query_read("
SELECT *
FROM " . TABLE_PREFIX . "planners
WHERE memberid = " . $vbulletin->userinfo['userid'] . "
ORDER BY id DESC LIMIT 1
");



$planners_array = array();
$planners_array[] = $vbulletin->db->fetch_array($planners);

$menuids = $planners_array[0][menuids];

$menus = $vbulletin->db->query_read("
SELECT id, shortdescription
FROM " . TABLE_PREFIX . "menus
WHERE id IN ($menuids)
");

}

$menus_temp = array();
while ($menu = $vbulletin->db->fetch_array($menus))
{
$menus_temp[$menu[id]] = $menu;
}

$menus_array = array();
$id_order = explode(',', $menuids);

foreach ($id_order AS $id)
{
$menus_array[] = $menus_temp[$id];
}

echo "<table style='border:none;width:1070px;'>
<tr>
<td style='border:none;width:70px'>&nbsp;</td>";
for($n = 0; $n < $planner_columns; $n++) {
echo " <td style='color:#FF0000;width:140px;border:none;paddi ng:5px;'><strong>" . $days_of_week[$n] . "</strong></td>";
}
echo " </tr>";
for($i = 0; $i < $planner_rows; $i++) {
echo " <tr>
<td style='color:#FF0000;width:70;border:none;text-align:right;padding-right:5px;'><strong>" . $meals[$i] . "</strong></td>";
$list_menu = $i;
for($ii = 0; $ii < $planner_columns; $ii++) {
echo "<td style='width:140;border:1px solid black;padding:5px;'>" . ($list_menu +1) . " " . $menus_array[$list_menu]['shortdescription'];

$list_menu += $planner_rows;
echo "</td>";
}
echo "</tr>";
}
echo "</table>";
Thank you so much for your help! :)

I'm not sure exactly how yet, but originally it was listing the menu descriptions in the order that they were entered into the database instead of by the order that they're listed in $menuids. But with your help I was able to get them to display exactly how I want them.

Thank you again!

--------------- Added 1230882971 at 1230882971 ---------------

Unless you have something that you really want to point out with this script the way I have it, this can be marked as SOLVED.

Thanks again! :)