Email sales reports with while statment

[eldan88]

Hey Guys. I have written some code that mails out sales reports for the past week that stores has done.

In my while loop it goes through all the stores and query's their sales reports and then stores it in the $result variable.

($result = mysql_num_rows($query)

 

 

When it emails the reports it sends out each email individual for each store. (I know the reason for that  is because its a while loop)Do you guys know of a way where I can have all the stores sales report be sent out to one email.

 

Another thing is that it keeps repeating the while loop, it doesn't seem to stop. How do can I stop  the while loop stop after it reached the last store ID in the DB.

 

Below is my code. Any help would be really appreciated!

while($stores = mysql_fetch_array($store_query)) {
$store_id = $stores['store_id'];
 //foreach ($id as $store_id) {
  $sql  = "SELECT * FROM checkout_page WHERE store_id = {$store_id} AND DATE(checkout_date) > CURDATE() - INTERVAL 7 DAY";
 $query = mysql_query($sql);
$result = mysql_num_rows($query);
if($result < 7) {
$sales_reports =  "Store id " . $store_id ." Result ". $result . "<br/>";
}
mail("myemail@myemail.com","Store Sales Reports","$sales_reports");

// }// end of foreach 
}//end of while loop

[Psycho]

First off - NEVER run queries in loops. You should do ONE query using a JOIN. Show the query you are running to get the $store_query result. Then we can show the proper way to do this.

 

EDIT: Also, are you only wanting the TOTAL sales for each store? I see you are only outputting results if there were less than 7 results. So, I assume you did that to prevent a lot of records being in the output.

Edited January 11, 2014 by Psycho

[Psycho]

The following is a shot in the dark since I don't know your table structures

 

 

//Run ONE query to get the results for ALL stores
$query = "SELECT s.store_id, s.store_name,
                 COUNT(cp.store_id) as sales_count, SUM(cp.amount) AS total_sales
          FROM stores AS s
          LEFT JOIN checkout_page AS cp
              ON s.store_id = cp.store_id
              AND DATE(cp.checkout_date) > CURDATE() - INTERVAL 7 DAY
          GROUP BY s.store_id
          ORDER BY s.store_id, cp.checkout_date"
$result = mysql_query($query);

$sales_report = ='';
while($row = mysql_fetch_assoc($result))
{
    $sales_report .=  "Store: ({$row['store_id']}) {$row['store_name']}, Sales: {$row['sales_count']}, Amount: \${$row['total_sales']}<br/>";
}//end of while loop

//Send email of COMPLETE sales reports
mail("myemail@myemail.com","Store Sales Reports","$sales_reports");

[eldan88]

Physco. Thanks for your reply!! That I will never do again(Running queries in loops)

 

I have never seen a query statement like that one you posted.  Can you please tell me why is there a "s." appended  to the store_id and store_name, and why is there a "cp." appended to the amount an store_id??

[Psycho]

Just as you can name a field in your output

 

SELECT COUNT(id) AS id_count

 

You can also name a table in the query

 

SELECT *
FROM table1 AS tb1

 

When only querying one table this really has no value. But, when you are querying from multiple tables you can use them to reference the different fields from those two tables.

 

Example (a is a reference to the author table and b is a reference to the books table)

 

SELECT a.author_name,
       b.book_name
FROM authors AS a
JOIN books ON a.author_id = b.author_id
ORDER BY a.author_name

 

This really becomes helpful when you have fields with the same name between tables and you need to reference exactly which one you want.

[eldan88]

Psycho. Okay I see what you mean to. I am new to this and have to get a little antiquated, though it doesn't seem to complicated. I have been doing some research online, and found some information about sql alias.

 

What I am a little confused about is you mentioned the following sql statement which was

SELECT *
FROM table1 AS tb1

I saw online there was a statement with out the "AS"

 SELECT s.first_name FROM student_details s; 

Also I don't get why you put an "s."before first name??
 why can't it be simply

 SELECT first_name FROM student_details AS s; 

Thanks again!

[kicken]

I saw online there was a statement with out the "AS"

The AS is optional. I tend to leave it out when I alias tables, but include it when aliasing a column. No particular reason why, just preference/style.

 

Also I don't get why you put an "s."before first name??

 why can't it be simply

The table prefix is only required if the same field name is used in multiple tables used in the query. If the field names are unique you can leave the table prefix off if you want. Including it however makes it clear exactly which table/field you are selecting. It's all about readability.

[Psycho]

 

Also I don't get why you put an "s."before first name??

 why can't it be simply

 

 

The table prefix is only required if the same field name is used in multiple tables used in the query. If the field names are unique you can leave the table prefix off if you want. Including it however makes it clear exactly which table/field you are selecting. It's all about readability.

 

As Kicken stated it isn't required IF the field you are referencing only exists in one table. But, here's the thing. You will save yourself a ton of time and effort in preventing and resolving errors by being consistent. Think about what happens when you come back to a query you wrote weeks/months previously that you need to either modify or debug.

 

If I have a query where I am pulling data from multiple tables I try to follow this process:

SELECT u.user_id, u.name, u.email, u.reg_date,
       p.post_id, p.post_title, p.post_date,
       r.reply_id, r.reply_date
FROM users AS u
LEFT JOIN posts AS p
  ON u.user_id = p.user_id
LEFT JOIN replies AS r
  ON p.post_id = r.post_id
WHERE p.post_date > '$report_start_date'

I alias all my tables and preference each field with the table alias. Plus, I include a line break in the SELECT fields based upon which table the fields come from. This makes it much, much easier to visually 'see' what the query is doing. I'm not saying this is the best way. Just that you should determine a process that works for you - and follow it.

Edited January 12, 2014 by Psycho

[eldan88]

Kicken. Thanks for making that clear.

 

Psycho thanks for showing me an example. Although I am little bit confused what does the statement do ...

LEFT JOIN posts AS p
  ON u.user_id = p.user_id

and this

WHERE p.post_date > '$report_start_date'

Sorry I am just really new to this, and is the first time I heard about Table Alias.

Edited January 14, 2014 by eldan88

[Psycho]

Psycho thanks for showing me an example. Although I am little bit confused what does the statement do ...

LEFT JOIN posts AS p
  ON u.user_id = p.user_id

 

There are whole books on how to work with a database. Trying to teach it in a forum post is not going to be productive. I suggest going through the tutorial as Tizag. You can jump to the sections about JOINs, but I suggest reading it from the start. Each section is pretty short. http://www.tizag.com/mysqlTutorial/

 

and this

WHERE p.post_date > '$report_start_date'

 

That one is simple. It simply states to only return records where the post_date is greater than the value of $report_start_date. This was just an example, but let's say you have a page where you let the user run a report on all posts and one of the parameters was a date that the user can set which identifies the period from which the report should start.

[eldan88]

Thanks again!! I am going to read through the information on the link you provided. You also mentioned that its good to use table aliasing when a different tables share the same column. But in my case there is one only one table that doesn't share the same column information.

 

Then I don't need to really use table aliasing? Correct?

[Psycho]

Thanks again!! I am going to read through the information on the link you provided. You also mentioned that its good to use table aliasing when a different tables share the same column. But in my case there is one only one table that doesn't share the same column information.

 

Then I don't need to really use table aliasing? Correct?

 No, you are getting data from TWO tables. Right now, you run one query then loop through that result set to query a different table. That is the wrong way to do that. You should run ONE query with a JOIN between the two tables - which I provided in my second reply.

[eldan88]

Hey Psycho. I am only querying the checkout_page  table and that's all, I don't understand where do you see that I am data from 2 tables??

[Psycho]

Hey Psycho. I am only querying the checkout_page  table and that's all, I don't understand where do you see that I am data from 2 tables??

 

Then you may be querying the same table twice which is just as bad. I can't see the first query, but it's obvious there is one. I already covered this previously but I guess I need to repeat it - NEVER RUN QUERIES IN LOOPS. Based on the code you originally posted, you are running a query, then looping through the results of that query to run additional queries:

 

<?php
//This is running through the results of the FIRST query
while($stores = mysql_fetch_array($store_query))
{
    $store_id = $stores['store_id'];
    //This is the SECOND query - they should be combined!!!
    $sql = "SELECT * FROM checkout_page WHERE store_id = {$store_id} AND DATE(checkout_date) > CURDATE() - INTERVAL 7 DAY";
    $query = mysql_query($sql);
    $result = mysql_num_rows($query);
    if($result < 7)
    {
        $sales_reports = "Store id " . $store_id ." Result ". $result . "<br/>";
    }
    mail("myemail@myemail.com","Store Sales Reports","$sales_reports");
}//end of while loop
?>

The code starts with a while() loop to extract the records from the result set from the FIRST query. You extract the store id from each record from that result set only for the purpose of running the SECOND query (which is run in loops). Again, this is the absolute wrong way to do this. You want to create ONE query to get all the data. This is accomplished by JOINing tables in your query. I created an example of how that might work for what you are trying to achieve in my second response. But, I stated that since I didn't know your table structure I couldn't verify if it would work or not.

Edited January 18, 2014 by Psycho

[eldan88]

Sorry. You where right. I checked my code again and there was another table I was querying. Thanks for seeing this. I will make the changes to the current query, and start querying the tables the you showed me. As always THANK A LOT! For your help and proper guidance.