Solved

Query Question

Posted on 2007-03-26
28
287 Views
Last Modified: 2013-12-13
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
Comment
Question by:lvollmer
  • 16
  • 7
  • 4
  • +1
28 Comments
 

Author Comment

by:lvollmer
ID: 18794230
ADDITIONAL NOTES: I need this done in PHP
0
 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18794270
you can try query as

SELECT * FROM table ORDER BY field DESC LIMIT 4
0
 

Author Comment

by:lvollmer
ID: 18794289
aamir - I need to query every table in that database

from wp_1_post to wp_76_post
0
 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18794320
you mean you would like to have a loop from 1 to 76 and then query at each iteration?
0
 

Author Comment

by:lvollmer
ID: 18794341
yes

and i need the to store 4 posts in 4 different variables. Those variables should be the newest post made (postdate field).
0
 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18794360
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
 

Author Comment

by:lvollmer
ID: 18794395
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
 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18794435
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
 

Author Comment

by:lvollmer
ID: 18794464
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
 
LVL 12

Expert Comment

by:AdrianSRU
ID: 18794572
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
 

Author Comment

by:lvollmer
ID: 18794667
Adrian - I need the latest 4 posts out of all of the blogs - not 4 latest posts of each blog.
0
 
LVL 12

Expert Comment

by:AdrianSRU
ID: 18794771
>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
 

Author Comment

by:lvollmer
ID: 18794816
ahhh I see. How do I store the 4 results in variables?
0
 

Author Comment

by:lvollmer
ID: 18794876
the final four results that is.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 12

Expert Comment

by:AdrianSRU
ID: 18794879
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
 

Author Comment

by:lvollmer
ID: 18794937
i will try this thanks
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18795037
>>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
 

Author Comment

by:lvollmer
ID: 18795276
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
 

Author Comment

by:lvollmer
ID: 18795593
$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
 
LVL 12

Expert Comment

by:AdrianSRU
ID: 18795773
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
 

Author Comment

by:lvollmer
ID: 18795807
im getting a query empty message also
0
 

Author Comment

by:lvollmer
ID: 18796893
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
 
LVL 12

Expert Comment

by:AdrianSRU
ID: 18797089
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
 

Author Comment

by:lvollmer
ID: 18800751
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
 
LVL 12

Expert Comment

by:AdrianSRU
ID: 18800850
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
 

Author Comment

by:lvollmer
ID: 18800902
adrian - it is selecting a database, I just took out   that information for the post.

0
 
LVL 12

Accepted Solution

by:
AdrianSRU earned 500 total points
ID: 18801422
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
 

Author Comment

by:lvollmer
ID: 18801487
It worked! I thought I changed that query over but I guess I didn't save the changes. Thanks!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now