The Arcive of Official vBulletin Modifications Site.

Dax IX · #1 12-31-2008, 07:58 AM

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...

PHP Code:


			
$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.

(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!

[EDIT]
Realized I needed to change a couple things.
[/EDIT]

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

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

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

Dismounted · #2 12-31-2008, 09:48 AM

PHP Code:


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

Dax IX · #3 12-31-2008, 10:02 AM

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 [DATE]1230731403[/DATE] at [TIME]1230731403[/TIME] ---------------

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:

PHP Code:


			
$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

)

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 · #4 12-31-2008, 03:53 PM

Probably something more along these lines:

PHP Code:


			
while ($menus_array = $vbulletin->db->fetch_array($menus))
    {
echo print_r($menus_array); 
    }

Dismounted · #5 01-01-2009, 03:29 AM

This is what you want:

PHP Code:


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

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

Dax IX · #6 01-01-2009, 05:39 PM

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 · #7 01-02-2009, 03:17 AM

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.

PHP Code:


			
$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 · #8 01-02-2009, 04:19 AM

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:

PHP Code:


			
$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;padding: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 · #9 01-02-2009, 04:51 AM

I don't see why you needed to results in any specific order. Replace what I posted before with this:

PHP Code:


			
$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:

PHP Code:


			
$menus_array[$menuid]['shortdescription']

Dax IX · #10 01-02-2009, 05:11 AM

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.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.04984 seconds Memory Usage 2,308KB Queries Executed 11 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (1)ad_showthread_firstpost (1)ad_showthread_firstpost_sig (1)ad_showthread_firstpost_start (9)bbcode_php (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)navbar (3)navbar_link (120)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (10)post_thanks_box (10)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (10)post_thanks_postbit_info (10)postbit (10)postbit_onlinestatus (10)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!