Query Question

ADDITIONAL NOTES: I need this done in PHP

you can try query as

SELECT * FROM table ORDER BY field DESC LIMIT 4

aamir - I need to query every table in that database

from wp_1_post to wp_76_post

you mean you would like to have a loop from 1 to 76 and then query at each iteration?

yes

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

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

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

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.

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.

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

Adrian - I need the latest 4 posts out of all of the blogs - not 4 latest posts of each blog.

>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

ahhh I see. How do I store the 4 results in variables?

the final four results that is.

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

i will try this thanks

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

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

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

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:
https://www.experts-exchange.com/questions/21673918/Warning-mysql-fetch-array-supplied.html

It seems to deal with the messages you are getting.


--Adrian

im getting a query empty message also

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" ];

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

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); ?>

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

adrian - it is selecting a database, I just took out   that information for the post.

It worked! I thought I changed that query over but I guess I didn't save the changes. Thanks!