Pass variable to mysql statement in foreach loop

phillyrob0817

Is there a way to use another variable in a mysql select where statement for inside a foreach loop.

Ex:

<?php

This works fine.

foreach ($range as $b)
        {

 $result = $conn->query("select count(*) as mycount1  from $table where date >= '$date' and (Ball1  = $b or Ball2  = $b or Ball3  = $b or Ball4  = $b or Ball5 = $b)");

  while ($row = $result->fetch_object()) {
   $count  = $row->mycount1;
   echo  $b." ".$count."<br>";
   }

  }

But I want to be able to use this variable string below* in my statement so I can change what comes after the "and" in the statement.
Is there a way to do that?

*$ballsB5 = ("Ball1  = $b or Ball2  = $b or Ball3  = $b or Ball4  = $b or Ball5 = $b");


foreach ($range as $b)
        {

   $result = $conn->query("select count(*) as mycount1  from $table where date >= '$date' and $ballsB5");

  while ($row = $result->fetch_object()) {
   $count  = $row->mycount1;
   echo  $b." ".$count."<br>";
   }

  }

NogDog

If the variables are only for values within a VALUE() expression or in a WHERE clause, then the best way would be to use prepared statements with bound parameters (assuming you're using PDO, MySQLi, or other extension that supports it).

In other cases, you could build a string with place-holders that could then use the loop values in a sprintf() function to rewrite the query in each iteration.

phillyrob0817

Thanks for the reply. I am using Mysqli. I will look into you advice. Thank you.

johanafm

phillyrob0817;11046027 wrote:

and (Ball1  = $b or Ball2  = $b or Ball3  = $b or Ball4  = $b or Ball5 = $b)");

Not knowing what Balls 1 to 5 are, I'm still guessing you need to normalize your table by creating a separate ”ball” relation. Not knowing the contents of $table, I will call that relation ”tbl”. Because ”date” is a reserved word in SQL, I have replaced that identifier by ”created”. If you stick with ”date” you will have to quote it according to your database. The SQL standard delimiter for quoting identifiers is " (double quote). MySQL default is ` back-tick (which is convenient to use when creating SQL statements in PHP.

create table ball (
    id        int unsigned primary key auto_increment, -- this would be 1 - 5, one entry for each of the previous Ball1 to Ball5 fields
    -- possibly other fields
);

You would then also need a table that links tbl to ball

create table tbl_ball (
    tbl_id int unsigned,
    ball_id int unsigned,
    FOREIGN KEY (tbl_id) REFERENCES tbl(id),
    FOREIGN KEY (ball_id) REFERENCES ball(id),
    PRIMARY KEY (tbl_id, ball_id)
);

This would now turn your select statement into

SET @b := 2;
SET @date := '2015-01-01';

SELECT count(*) AS mycount1
FROM tbl
WHERE created >= @date AND EXISTS (SELECT 1 FROM tbl_ball WHERE tbl_id = tbl.id AND ball_id = @b);

It may look more complicated than your approach, but it solves a lot of problems in the long run.

Also: +1 for prepared statements.

cyberlew15

I think you might be coming at this all wrong, it looks like you need to group by and count on the value where the value is in a set of values from the array of values made in the range, which should be able to be imploded by ", ".

Benefits of this approach

more efficient for the DB
less work in PHP
one result you can still work with via fetch or fetchAll

There are some things I would suggest you change however.

Use PDO and $stmt->bindParam( ':nameofparam', $vartotakevalfrom, $paramtype );
This can help to avoid most SQL injection attempts and sanitize input
If you use $table in the SQL string wrap any vars with face brackets like this {$table} and also validate the table!

If you are up for continuing with a slight change of architecture, Id be happy to walk you through it 🙂

johanafm

cyberlew15;11046191 wrote:
I think you might be coming at this all wrong, it looks like you need to group by and count on the value where the value is in a set of values from the array of values made in the range, which should be able to be imploded by ", ".

Downsides of this approach:
It is still the wrong solution to the problem. The proper solution is not to find less inefficient ways of dealing with de-normalized data. The proper solution is to normalize data. For more info, see Database normalization.

cyberlew15

Hi, I have not defined the Schema, I am merely suggesting an improvement to the PHP by using an alternative QUERY of the existing Schema. I agree that ball 1-5 should be stored in a separate table with a priority to denote ball1-5, but again... Not the schema architect lol. Also I do believe GROUP BY would still be needed as well as COUNT to fulfill, and IN would still be the most efficient way to get all the counts... Feel free to correct again if I am wrong.