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.

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

    and what query is that?

      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.

        that can be done with a single query:

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

          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.

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

              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'];}

                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/showthread.php?s=&threadid=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.

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

                    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>';
}

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

                      Au Contraire.

                        8 years later
                        nemonoman;10564295 wrote:

                        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.

                          Write a Reply...