foreach and MySQL WHERE= query

[viveleroi0]

I have a field that can contain up to five different numbers seperated by a semicolon.

I explode that string apart at each semicolon and I want to run the same MySQL query foreach result.

so...

my string is 123;456;789

that is seperated into:

$pieces[0] = 123
$pieces[1] = 456
$pieces[2] = 789

I want to run the same query on each of those three. I made a foreach loop, but I cannot figure out how to use the next part of the returned array each time.

[devinemke]

Originally posted by viveleroi0
...I want to run the same query on each of those three...

and what query is that?

[viveleroi0]

SELECT * FROM table WHERE AC="$pieces[0]"

But then that query would need to be executed again but with the next value in the array:

SELECT * FROM table WHERE AC="$pieces[1]"

and so on.

[devinemke]

that can be done with a single query:

$result = mysql_query('SELECT * FROM table WHERE AC IN(' . implode(',', $pieces) . ')'); 

[viveleroi0]

But I need to be able to seperate the counts for each unique value in the array... not count how many exist in total.


Like: 123 = 1,500 counts, 456 = 2,000 counts.

Not 123 and 456 together = 3,500 counts.

[devinemke]

that can still be done with a single query:

$result = mysql_query('SELECT COUNT(*), AC FROM table WHERE AC IN(' . implode(',', $pieces) . ') GROUP BY AC'); 

[viveleroi0]

How would I be able to combine the same query (or at least something to get the same effect) in this:

$result = mysql_query("SELECT COUNT(*) AS count, DATE_FORMAT(date, '%Y-%m-%d') AS date 
FROM " . $DB['table']['calltracking'] . " 
WHERE " . $DB['field']['calls']['ac'] . "=\"$pieces\" 
AND " . $DB['field']['calls']['date'] . " BETWEEN '" . $date1 . "' AND '" . $date2 . "' GROUP BY date");
while ($row = mysql_fetch_assoc($result)) {$array[$row['date']] = $row['count'];} 

[nemonoman]

The problem is that you are trying to jam multiple values into a single field. You really should change the design -- not hard to do -- and keep these values in a separate table.

See my answer to silverx:

http://www.phpbuilder.com/board/show...eadid=10287286

You have similar (inadequate) database designs. Don't be afraid to reform your data in a better design -- the time you save working with a good design as you go forward will more than compensate for the time you spend making it right.

[viveleroi0]

I fail to see how wanting to perform the same query (except with a different value in the WHERE clause) in order for the results to appear on one page means that I have an inefficient database design.

I have been designing databases for ten years and my design is far from inefficient. I simpy want to count how many rows match a date and an area code.

My database has simply two fields for this query - DATE and AC. Explain to me how much more efficient that can become.

The problem lies in the fact that I a) want to count matches for EACH DATE between a range selected on a form and B) perform the same query for up to five different area codes (SO THE RESULTS SHOW ON ONE PAGE).

That is more a problem of my lack of advanced knowledge of PHP that a failure of my database.

All I am asking is how to increment an array variable to the next element when in a FOREACH loop.

[devinemke]

here's the basic idea:

// some example input for testing:
$start_date = '2004-10-01';
$end_date = '2004-10-25';
$area_codes = array('123', '456', '789');

foreach ($area_codes as $value)
{
    echo 'results for ' . $value . '<br>';
    $result = mysql_query("SELECT COUNT(*) AS count, date FROM table WHERE ac = '" . $value . "' AND date BETWEEN '" . $start_date . "' AND '" . $end_date . "' GROUP BY date");
    while ($row = mysql_fetch_assoc($result))
    {
        echo 'count = ' . $row['count'] . ' date = ' . $row['date'] . '<br>';
    }
    echo '<br><br>';
} 

[nemonoman]

That is more a problem of my lack of advanced knowledge of PHP that [sic] a failure of my database.

Au Contraire.

[viveleroi0]

Au Contraire.

I stumbled across this forum thread after remembering my old username and poking through a google search. I was still only a few years into programming when I had originally asked this question, but I've been doing it ever since and for the last eight years or so, it's been my day job.

I was cringing while reading my original question - *of course* that was a horrible database design to begin with and it wasn't my query I had trouble with, it was trying to band-aid the database problem. Could have easily solved the issue by separating the area code, prefix, and suffix into separate columns and querying those.

As the thread continued I was quite surprised to see myself be a big dick and claim my database design was totally efficient and I was so smart, blah blah.

I don't even remember this particular issue but I do know which project it was.

Anyway, sorry for being a jerk. Hope 9 years later is ok, lol.