• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Query Question

I have a database with a lot of tables.

The tables are named like this:

wp_1_post
wp_2_post
wp_3_post
...wp_76_post

and so on.

Each table has the same structure. I need to query each table and return 4 results based on the posttime row. I need the most recent 4 posts and the post title - the row name is title.

Is this possible?
0
lvollmer
Asked:
lvollmer
  • 16
  • 7
  • 4
  • +1
1 Solution
 
lvollmerAuthor Commented:
ADDITIONAL NOTES: I need this done in PHP
0
 
Aamir SaeedCommented:
you can try query as

SELECT * FROM table ORDER BY field DESC LIMIT 4
0
 
lvollmerAuthor Commented:
aamir - I need to query every table in that database

from wp_1_post to wp_76_post
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Aamir SaeedCommented:
you mean you would like to have a loop from 1 to 76 and then query at each iteration?
0
 
lvollmerAuthor Commented:
yes

and i need the to store 4 posts in 4 different variables. Those variables should be the newest post made (postdate field).
0
 
Aamir SaeedCommented:
how would you like to manipulate it?

1; display data on each iteration
                 OR
2; first collect all the data and then use it later
0
 
lvollmerAuthor Commented:
collect then use.

What i have to do is query all those databases. then I have to echo 4 lines

IE:

"The newest Post is : <newest post title here>"
"The second newest post is <2nd newest post title>"
"The third newest post is <3rd newest post title>"
"The fourth Newest Post is <4th newest post title here>"
0
 
Aamir SaeedCommented:
this is a bit personal but is it requirement to use the table structure like this?
wp_1_post
wp_2_post
wp_3_post
...wp_76_post

you can do it with just one table and that would be very much fast as compare to it. if you want to keep track of wp_1, wp_2, ... , you just can take a flag field for it.
0
 
lvollmerAuthor Commented:
wp_1, wp_2, represent different blogs. We can't have all the different blogs in the same table. That is the problem. We want to automate the list of the newest blogs that are updated.
0
 
AdrianSRUCommented:
You could probably do this with a huge SELECT statement with lots of UNIONs:

(SELECT * FROM wp_1_post ORDER BY postdate DESC LIMIT 4)
UNION ALL
(SELECT * FROM wp_2_post ORDER BY postdate DESC LIMIT 4)
UNION ALL
(SELECT * FROM wp_3_post ORDER BY postdate DESC LIMIT 4)
UNION ALL
...
(SELECT * FROM wp_76_post ORDER BY postdate DESC LIMIT 4)
ORDER BY postdate DESC LIMIT 4;

I'm not 100% certain if there is an upper limit on the number of UNIONs that can be in a single query.  I have nevery tried this many before, but it should work.

Each table is queried, sorted, and limited to it's 4 latest posts.  Then at the end, the 4 posts from each table are sorted again and only the top 4 posts from any table are returned.  This is essentially the same as querying each table individually and keeping track of the 4 latest records but it is done in one query and you don't have to do any processing on the application end to get your results.

Hope this helps!


--Adrian
0
 
lvollmerAuthor Commented:
Adrian - I need the latest 4 posts out of all of the blogs - not 4 latest posts of each blog.
0
 
AdrianSRUCommented:
>I need the latest 4 posts out of all of the blogs - not 4 latest posts of each blog.

That is what my query gives you.  The SELECT of each table is limited to the latest 4 posts only to pare down the number of records that need to be sorted when the UNIONs put all of the results together.  It is the final "ORDER BY postdate DESC LIMIT 4;" that sorts the top results from each table and limits it to the 4 latest.


--Adrian
0
 
lvollmerAuthor Commented:
ahhh I see. How do I store the 4 results in variables?
0
 
lvollmerAuthor Commented:
the final four results that is.
0
 
AdrianSRUCommented:
PHP isn't my specialty but I think this is what you need:

$result = mysql_query("(SELECT * FROM wp_1_post ORDER BY postdate DESC LIMIT 4) UNION ALL (SELECT * FROM wp_2_post ORDER BY postdate DESC LIMIT 4) UNION ALL (SELECT * FROM wp_3_post ORDER BY postdate DESC LIMIT 4) UNION ALL ... (SELECT * FROM wp_76_post ORDER BY postdate DESC LIMIT 4) ORDER BY postdate DESC LIMIT 4;");

if( $r=mysql_fetch_array( $result ) )
    $posttitle_1 = $result[ "posttitle" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_2 = $result[ "posttitle" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_3 = $result[ "posttitle" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_4 = $result[ "posttitle" ];


--Adrian
0
 
lvollmerAuthor Commented:
i will try this thanks
0
 
LowfatspreadCommented:
>>wp_1, wp_2, represent different blogs. We can't have all the different blogs in the same table. That is the problem.

why can't you have all the blogs in the one table? you have a very simple system then and all you'd need
would be an additional column to hold the blog name/category...?

the current situation is going to give you performance problems in the long (well short proably) run...
0
 
lvollmerAuthor Commented:
lowfatspread - this is the way the blog software configures new blogs.

When you go into the admin and create "Jimmys Blog" it creates a new table called wp_1_blog. Creating "Bobbys Blog" will create wp_2_blog

etc etc
0
 
lvollmerAuthor Commented:
$result = mysql_query("(SELECT * FROM wp1_posts ORDER BY postdate DESC LIMIT 1) UNION ALL
(SELECT * FROM wp2_posts ORDER BY post_date DESC LIMIT 1) UNION ALL
(SELECT * FROM wp3_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp4_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp5_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp6_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp7_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp8_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp9_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp10_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp11_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp12_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp13_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp14_posts ORDER BY post_date DESC LIMIT 4) ORDER BY post_date DESC LIMIT 4;");

if( $r=mysql_fetch_array( $result ) )
    $posttitle_1 = $result[ "post_title" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_2 = $result[ "post_title" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_3 = $result[ "post_title" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_4 = $result[ "post_title" ];
mysql_close($result);


Gives me this error:


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/14612/domains/.com/html/blog_refer.php on line 26

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/14612/domains/.com/html/blog_refer.php on line 28

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/14612/domains/.com/html/blog_refer.php on line 30

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/14612/domains/.com/html/blog_refer.php on line 32

Warning: mysql_close(): supplied argument is not a valid MySQL-Link resource in /home/14612/domains/.com/html/blog_refer.php on line 34



Is my syntax wrong?
0
 
AdrianSRUCommented:
I did make a mistake in my code, the if statements need to be like this:

if( $r=mysql_fetch_array( $result ) )
    $posttitle_1 = $r[ "posttitle" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_2 = $r[ "posttitle" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_3 = $r[ "posttitle" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_4 = $r[ "posttitle" ];

However, that doesn't seem to be your problem.  Check out this question:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_21673918.html

It seems to deal with the messages you are getting.


--Adrian
0
 
lvollmerAuthor Commented:
im getting a query empty message also
0
 
lvollmerAuthor Commented:
I have resolved the SQL issue. The SQL works fine now in SQL monitor.

The problem I am having now is that the data does not display when these values are echoed:
if( $r=mysql_fetch_array( $result ) )
    $posttitle_1 = $r[ "posttitle" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_2 = $r[ "posttitle" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_3 = $r[ "posttitle" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_4 = $r[ "posttitle" ];


0
 
AdrianSRUCommented:
How about if you use the array position instead of the column title:

$r[ 2 ] instead of $r[ "posttitle" ]

Replace 2 with the position of posttitle in the resultset (0 is the first).  I'm kind of reaching here because as I mentioned above, PHP isn't really my thing, but if that doesn't help then post your entire code as it is now and maybe I can find something.


--Adrian
0
 
lvollmerAuthor Commented:
Here is the code I have - I tried what you suggested and cannot get it to display a result.



<?

$link = mysql_connect("", "", "") OR DIE("Unable to connect to the database");
mysql_select_db("");
$result = mysql_query("(SELECT * FROM wp_1_posts ORDER BY post_date DESC LIMIT 1) UNION ALL
(SELECT * FROM wp_2_posts ORDER BY post_date DESC LIMIT 1) UNION ALL
(SELECT * FROM wp_3_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_5_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_6_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_7_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_8_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_9_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_10_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_11_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_12_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_13_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_35_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_43_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_55_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_65_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_75_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_77_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_78_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_79_posts ORDER BY post_date DESC LIMIT 4) UNION ALL
(SELECT * FROM wp_80_posts ORDER BY post_date DESC LIMIT 4) ORDER BY post_date DESC LIMIT 4;");
//$r = mysql_query($query);
//echo mysql_error();
if( $r=mysql_fetch_array( $result ) )
    $posttitle_1 = $result[ "post_title" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_2 = $result[ "post_title" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_3 = $result[ "post_title" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_4 = $result[ "post_title" ];

?>
<body>
<? echo $posttitle_1;?>
<br /><hr />
<? echo $posttitle_2;?>
<br /><hr />
<? echo $posttitle_3;?>
<br /><hr />
<? echo $posttitle_4;?>
<br /><hr />
<? mysql_close($link); ?>
0
 
AdrianSRUCommented:
I think that your query has to be failing because you aren't selecting a database.  Your mysql_select_db function has to specify the database that your tables are in.  You can confirm this by changing the line to:

$result = mysql_query(<query here>) or die("Error in query : " . mysql_error());


--Adrian
0
 
lvollmerAuthor Commented:
adrian - it is selecting a database, I just took out   that information for the post.

0
 
AdrianSRUCommented:
I just noticed, you didn't change the if statements as I mentioned a few posts ago.  The query values need to be comming from $r instead of $result.  $result represents the entire query resultset whereas $r is the next row that has been fetched from the resultset.  The if statements need to be:

if( $r=mysql_fetch_array( $result ) )
    $posttitle_1 = $r[ "post_title" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_2 = $r[ "post_title" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_3 = $r[ "post_title" ];
if( $r=mysql_fetch_array( $result ) )
    $posttitle_4 = $r[ "post_title" ];


--Adrian
0
 
lvollmerAuthor Commented:
It worked! I thought I changed that query over but I guess I didn't save the changes. Thanks!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 16
  • 7
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now