0

Not sure how to properly word this or do it, which is why I am here lol.

Here is current code:

$sql = "SELECT state FROM list WHERE lead_id = '$lead_id'";
    $result = mysqli_query($link, $sql, MYSQLI_USE_RESULT);
    if (!$result) 
    { 
        echo 'Error: ', $mysqli->error;
    }

    while($row = $result->fetch_assoc()){
    //I need some code here

    }

I'm pulling states from a table based on $lead_id, which is given in a previous statement. There's a few thousand records that this will go through and get the State for. I need the best way keep the count for each state in the while loop (or another way if someone suggests). I need to output the state counts in a table later on. Can anyone help me out with this? I'm not sure how to do it or how to go about it.

Thanks. I appreciate any help in advance.

dkeeper09
  • 535
  • 2
  • 9
  • 29
  • You're probably best doing a join from the previous query, so can you show that – Mark Baker Sep 08 '13 at 18:08
  • Pretty much the same thing, except the code above in the while loop: `$sql = "SELECT * FROM call_log WHERE MONTH(call_date) = MONTH(CURDATE())"; $result = mysqli_query($link, $sql, MYSQLI_USE_RESULT); while($row = $result->fetch_assoc()){ $lead_id = $row['lead_id']; //code above here}` – dkeeper09 Sep 08 '13 at 18:10
  • 1
    Start by modifying your initial query to something like `SELECT c.*, s.state FROM call_log c LEFT JOIN state s ON s.lead_id = c.lead_id WHERE MONTH(c.call_date) = MONTH(CURDATE())` - if you're pulling a few thousand call log records, this will reduce the number of time-consuming database queries by a few thousand – Mark Baker Sep 08 '13 at 18:14
  • Ok, then how do I get the state counts out of that? – dkeeper09 Sep 08 '13 at 18:16
  • It adds state as a column returned by the main query, which you can then loop through and count them as you process each call log record; but if all you need are the counts, and you don't do anything else with the call log data, then you can modify the query to return just the state counts – Mark Baker Sep 08 '13 at 18:18
  • Cool. Go it. Thanks Mark. – dkeeper09 Sep 08 '13 at 20:11
  • **By building SQL with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. – Andy Lester Sep 08 '13 at 23:53

0 Answers0