foreach and MySQL WHERE= query
Results 1 to 12 of 12

Thread: foreach and MySQL WHERE= query

  1. #1
    Senior Member viveleroi0's Avatar
    Join Date
    May 2002
    Posts
    277

    foreach and MySQL WHERE= query

    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.
    been here twice as long as I thought and know half of what I think

  2. #2
    chocoholic (with alcohol) devinemke's Avatar
    Join Date
    Aug 2002
    Location
    NYC
    Posts
    5,158

    Re: foreach and MySQL WHERE= query

    Originally posted by viveleroi0
    ...I want to run the same query on each of those three...
    and what query is that?

  3. #3
    Senior Member viveleroi0's Avatar
    Join Date
    May 2002
    Posts
    277
    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.
    been here twice as long as I thought and know half of what I think

  4. #4
    chocoholic (with alcohol) devinemke's Avatar
    Join Date
    Aug 2002
    Location
    NYC
    Posts
    5,158
    that can be done with a single query:
    PHP Code:
    $result mysql_query('SELECT * FROM table WHERE AC IN(' implode(','$pieces) . ')'); 

  5. #5
    Senior Member viveleroi0's Avatar
    Join Date
    May 2002
    Posts
    277
    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.
    been here twice as long as I thought and know half of what I think

  6. #6
    chocoholic (with alcohol) devinemke's Avatar
    Join Date
    Aug 2002
    Location
    NYC
    Posts
    5,158
    that can still be done with a single query:
    PHP Code:
    $result mysql_query('SELECT COUNT(*), AC FROM table WHERE AC IN(' implode(','$pieces) . ') GROUP BY AC'); 

  7. #7
    Senior Member viveleroi0's Avatar
    Join Date
    May 2002
    Posts
    277
    How would I be able to combine the same query (or at least something to get the same effect) in this:

    PHP Code:
    $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'];} 
    been here twice as long as I thought and know half of what I think

  8. #8
    Senior Member
    Join Date
    Dec 2002
    Location
    Asheville, NC
    Posts
    1,736
    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.
    Found your answer?
    Mark your thread RESOLVED!
    (under "THREAD TOOLS" menu)
    It's easy, it's fun and it helps everyone!

  9. #9
    Senior Member viveleroi0's Avatar
    Join Date
    May 2002
    Posts
    277
    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.
    been here twice as long as I thought and know half of what I think

  10. #10
    chocoholic (with alcohol) devinemke's Avatar
    Join Date
    Aug 2002
    Location
    NYC
    Posts
    5,158
    here's the basic idea:
    PHP Code:
    // 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>';


  11. #11
    Senior Member
    Join Date
    Dec 2002
    Location
    Asheville, NC
    Posts
    1,736
    That is more a problem of my lack of advanced knowledge of PHP that [sic] a failure of my database.
    Au Contraire.
    Found your answer?
    Mark your thread RESOLVED!
    (under "THREAD TOOLS" menu)
    It's easy, it's fun and it helps everyone!

  12. #12
    Senior Member viveleroi0's Avatar
    Join Date
    May 2002
    Posts
    277
    Quote Originally Posted by nemonoman View Post
    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.
    been here twice as long as I thought and know half of what I think

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •