Help me on this MYSQL QUERY I only want 1 query...

[Gayner]

 

Ok so i have no idea i only know how to run 1 query i can't use inner join or if i need to?

 

But this i waht I want to do!

 

 

SELECT * FROM users ORDER BY id DESC LIMIT 1

 

AND ALSO Select * from Prayers and Prayed so i can echo those out differently, so at the end of my result i can echo out this

 

"We have a total of XX Prayers and People have  XXX Prayed times.

 

Our newest member is ____

 

How do i make it only 1 big query do u mind? thx

[Deoctor]

Actually what is the question u are asking for??

  u want from the newest member ____

[Gayner]

Actually what is the question u are asking for??

  u want from the newest member ____

 

yea and count * from prayers and prayed and beable to display them echo in php

[Gayner]

like kinda like this but iuno what im doing help me..

query = "SELECT COUNT(prayers),(prayed) FROM users ORDER BY id DESC LIMIT 1";

[Deoctor]

if i am right u want the count of prayers and count of prayed for each player or any other thing?

 

by the way if possible paste me the structure with sample data of the table..

 

[Gayner]

if i am right u want the count of prayers and count of prayed for each player or any other thing?

 

by the way if possible paste me the structure with sample data of the table..

 

i just want to count all prayers/prayed... but i can't do that cause i have to LIMIT it to 1 to get only the newest member...

 

 

so like.. i only want to use 1 mysql query plz thx

 

[Deoctor]

SELECT COUNT(prayers),COUNT(prayed) FROM users WHERE id=1

[Gayner]

SELECT COUNT(prayers),COUNT(prayed) FROM users WHERE id=1

 

id = 1? i got count to work but i need to show newest user too his field name is user_name but i can only do that if i LIMIT 1 but if I LIMIT 1 how do i get count prayed still working or does that ignore the function ?

[ldb358]

i would just loop through

$results = mysql_query("SELECT * FROM users ORDER BY id DESC");
$i=0;
$first= '';
$prayers = 0;
$prayed = 0;
while($row= mysql_fetch_array($results)){
    if($i==0){
          $first = $row['user_name'];
          $i++;
    }
   $prayers += $row['prayers'];
   $prayed += $row['prayed'];
}
echo "We have a total of $prayers Prayers and People have  $prayed Prayed times.

Our newest member is $first";

[Deoctor]

limit 1 is the function to use to the multiple results to be reduced to one.

here u are using the count option obviously it will be one.

if u want the count for the newest member then u need to specify the max of the date of his joining or else u need to check his last updation

[Gayner]

$results = mysql_query("SELECT * FROM users ORDER BY id DESC");

$i=0;

$first= '';

$prayers = 0;

$prayed = 0;

while($row= mysql_fetch_array($results)){

    if($i==0){

          $first = $row['user_name'];

          $i++;

    }

  $prayers += $row['prayers'];

  $prayed += $row['prayed'];

}

echo "We have a total of $prayers Prayers and People have  $prayed Prayed times.

 

Our newest member is $first";

 

Sir this works nice, but only 1 problem, i can't be calling my whole user table each time a refresh happens, (my 1.99$/month server will crash in 2seoncds) is there anyway to LIMIT 1 to that query? Or is my proposal just impossible w/o 2 seperate queries? lol thanks for ur help man.. appreciate it alot broski.

[btherl]

So you want 3 data items:

 

1.  The most recent user's name

2.  The sum of the prayers column for all users

3.  The sum of the prayed column for all users

 

Is that right?  I'm a bit confused as to whether you want the sum or the count.  In any case, you should do it with two queries.

 

SELECT user_name FROM users ORDER BY id DESC LIMIT 1

SELECT SUM(prayed) as sum_prayed, SUM(prayers) as sum_prayers FROM users

 

I don't see any benefit to using one query only in this situation.  Mysql can probably do the first query quickly using an index.

 

If you find you have a performance problem with scanning the whole users table to find these counts, you can store the sums in a seperate table and update them every time prayers or prayed is updated for a user.

[Gayner]

my friend jsut helped me.. hella i love him no homo

 

here's SOLVED:

 

$query = "SELECT sum(prayers), sum(prayed),
(select user_name from users order by id desc limit 1) as `latest` from users";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
print_r($row);
$newest_member = $row['user_name'];

 

:-)

 

enjoy thx !!