call multiple selects in while loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

call multiple selects in while loop

Post by invisibled »

hey everybody,

I am trying to call information from two tables and print them out into a loop. Its for a wordpress template, and it has to be in two seperate tables. This is what i have right now.

Code: Select all

<?php
$thumb_query = "SELECT * FROM ti_thumb";
$thumb_mysql = mysql_query($thumb_query);
            
$post_query = "SELECT ID, post_title FROM ti_posts WHERE post_type = 'page' ORDER BY ID DESC";
$post_mysql = mysql_query($post_query);
            
while($post_row = mysql_fetch_assoc($post_mysql) && $thumb_row = mysql_fetch_assoc($thumb_mysql)): 
?>
  <li> 
      <img src="/images/tmp/<?php print $thumb_row['userfile_name']; ?>" />
      <div class="plus"> <a href="?page_id=<?php print $post_row['ID'];?>">( + )</div>
      <?php print $post_row['post_title'];?></a>
  </li>
            
<?php endwhile; ?>
The while loop only displays the second arguement (the $thumb_mysql) and i need it to get information from both tables. I think i'm just missing somthing silly. Any suggestions?
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: call multiple selects in while loop

Post by flying_circus »

This seems like something that could best be worked out in your SQL query. What is the relationship of the thumbnail and the post query?
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: call multiple selects in while loop

Post by invisibled »

whatever works i'm happy with! haha

Ok basicly i am listing a grid of images that link to a wordpress page. So when the user makes a new page, it will send all the normal wordpress data to the normal wordpress ti_post table and then to my ti_thumb table, witch stores the file name of the image he uploads.

The grid, showing all the pages will look like this

__________________
| |
| |
| ( + ) |
__________________
Page title

So it needs to grab the thumbnail from ti_thumb and the page title and page ID from the ti_post table. Make sence?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: call multiple selects in while loop

Post by RobertGonzalez »

Sounds to me like you need a simple join query.
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: call multiple selects in while loop

Post by invisibled »

could you elaborate a little bit. I looked into joins, but i dont really understand them.. never used a join
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: call multiple selects in while loop

Post by flying_circus »

invisibled wrote:whatever works i'm happy with! haha

Ok basicly i am listing a grid of images that link to a wordpress page. So when the user makes a new page, it will send all the normal wordpress data to the normal wordpress ti_post table and then to my ti_thumb table, witch stores the file name of the image he uploads.

The grid, showing all the pages will look like this

__________________
| |
| |
| ( + ) |
__________________
Page title

So it needs to grab the thumbnail from ti_thumb and the page title and page ID from the ti_post table. Make sence?
I understand what you are trying to do, but you still havent told me how the image thumbnail is related to the post. What is the similar ID between the two? Generally you'll have a database like so:

TABLE: Thumbnail
---------------------
Thumbnail_ID
Image_Path
---------------------

TABLE: Posts
---------------------
Post_ID
Post_Contents
Thumbnail_ID
---------------------


You see? The row from the Posts table would reference one of the thumbnails. So, if we know the ID of the thumbnail we are supposed to use, you can search the thumbnail table to find the path to the image. The relationship is the Thumbnail_ID on both tables. We can use a simple JOIN to get you going. However, if there is no relationship between the image and the post, how are we (or you) supposed to know which thumbnail goes with each post in your image grid?

In my example above, you could do a SQL query like:

SELECT * FROM Posts as p, Thumbnail as t WHERE p.Thumbnail_ID = t.Thumbnail_ID

You would get a result row consisting of:
---------------------
Post_ID
Post_Contents
Thumbnail_ID
Image_Path
---------------------

Then you would only need 1 condition (which is allowed) in your while loop.
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: call multiple selects in while loop

Post by invisibled »

ahh i see what you mean. Their is an ID column on both tables. When a page get published an ID gets sent to both tables so the id's will always be the same on the two tables
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: call multiple selects in while loop

Post by RobertGonzalez »

Can you run a SHOW CREATE TABLE `tableName`; query on each table and post the result here for us to see?
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: call multiple selects in while loop

Post by invisibled »

ti_thumb

Code: Select all

 
CREATE TABLE `ti_thumb` (\n  `ID` mediumint(9) NOT NULL auto_increment,\n  `userfile_name` varchar(255) NOT NULL,\n  PRIMARY KEY  (`ID`)\n) ENGINE=MyISAM AUTO_INCREMENT=65 DEFAULT CHARSET=latin1
ti_posts

Code: Select all

 
CREATE TABLE `ti_posts` (\n  
`ID` bigint(20) unsigned NOT NULL auto_increment,\n  
`post_author` bigint(20) NOT NULL default '0',\n  
`post_date` datetime NOT NULL default '0000-00-00 00:00:00',\n  
`post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',\n  
`post_content` longtext NOT NULL,\n  
`post_title` text NOT NULL,\n  
`post_category` int(4) NOT NULL default '0',\n  
`post_excerpt` text NOT NULL,\n  
`post_status` varchar(20) NOT NULL default 'publish',\n  
`comment_status` varchar(20) NOT NULL default 'open',\n  
`ping_status` varchar(20) NOT NULL default 'open',\n  
`post_password` varchar(20) NOT NULL default '',\n  
`post_name` varchar(200) NOT NULL default '',\n  
`to_ping` text NOT NULL,\n  
`pinged` text NOT NULL,\n  
`post_modified` datetime NOT NULL default '0000-00-00 00:00:00',\n  
`post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',\n  
`post_content_filtered` text NOT NULL,\n  
`post_parent` bigint(20) NOT NULL default '0',\n  
`guid` varchar(255) NOT NULL default '',\n  
`menu_order` int(11) NOT NULL default '0',\n  
`post_type` varchar(20) NOT NULL default 'post',\n  
`post_mime_type` varchar(100) NOT NULL default '',\n  
`comment_count` bigint(20) NOT NULL default '0',\n  
PRIMARY KEY  (`ID`),\n  KEY `post_name` (`post_name`),\n  
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`)\n) 
ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=utf8
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: call multiple selects in while loop

Post by RobertGonzalez »

So which column in the ti_posts table matches the ID column in ti_thumb?
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: call multiple selects in while loop

Post by invisibled »

Everah wrote:So which column in the ti_posts table matches the ID column in ti_thumb?
the ID column
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: call multiple selects in while loop

Post by RobertGonzalez »

so ti_posts.ID = ti_thumb.ID?
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: call multiple selects in while loop

Post by invisibled »

Everah wrote:so ti_posts.ID = ti_thumb.ID?
correct sir
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: call multiple selects in while loop

Post by RobertGonzalez »

[sql]SELECT     p.*, t.* FROM     ti_posts AS p INNER JOIN     ti_thumb AS t ON    t.ID = p.ID;[/sql]

On another note, I would really consider consolidating the tables. It makes sense to do so in this situation.
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: call multiple selects in while loop

Post by invisibled »

GREAT. I understand the process now. Thank you so much!
Post Reply