Page 1 of 2

call multiple selects in while loop

Posted: Mon Apr 28, 2008 4:49 pm
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?

Re: call multiple selects in while loop

Posted: Mon Apr 28, 2008 5:12 pm
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?

Re: call multiple selects in while loop

Posted: Mon Apr 28, 2008 6:13 pm
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?

Re: call multiple selects in while loop

Posted: Mon Apr 28, 2008 6:40 pm
by RobertGonzalez
Sounds to me like you need a simple join query.

Re: call multiple selects in while loop

Posted: Mon Apr 28, 2008 7:23 pm
by invisibled
could you elaborate a little bit. I looked into joins, but i dont really understand them.. never used a join

Re: call multiple selects in while loop

Posted: Mon Apr 28, 2008 8:41 pm
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.

Re: call multiple selects in while loop

Posted: Mon Apr 28, 2008 11:54 pm
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

Re: call multiple selects in while loop

Posted: Tue Apr 29, 2008 1:37 am
by RobertGonzalez
Can you run a SHOW CREATE TABLE `tableName`; query on each table and post the result here for us to see?

Re: call multiple selects in while loop

Posted: Tue Apr 29, 2008 3:19 am
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

Re: call multiple selects in while loop

Posted: Tue Apr 29, 2008 11:44 am
by RobertGonzalez
So which column in the ti_posts table matches the ID column in ti_thumb?

Re: call multiple selects in while loop

Posted: Tue Apr 29, 2008 1:22 pm
by invisibled
Everah wrote:So which column in the ti_posts table matches the ID column in ti_thumb?
the ID column

Re: call multiple selects in while loop

Posted: Tue Apr 29, 2008 1:24 pm
by RobertGonzalez
so ti_posts.ID = ti_thumb.ID?

Re: call multiple selects in while loop

Posted: Tue Apr 29, 2008 1:36 pm
by invisibled
Everah wrote:so ti_posts.ID = ti_thumb.ID?
correct sir

Re: call multiple selects in while loop

Posted: Tue Apr 29, 2008 4:30 pm
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.

Re: call multiple selects in while loop

Posted: Tue Apr 29, 2008 8:15 pm
by invisibled
GREAT. I understand the process now. Thank you so much!