Hello,
Here is my query code. It gets the variables from a form that offers a possibility to filter trading results
$sql = "INSERT INTO `filtered`
(id, username, ticket, o_time, type, size, item, o_price, s_l, t_p, c_time, c_price, profit)
SELECT *
FROM `data`
WHERE c_time BETWEEN :start_date AND :end_date
AND TIME(`c_time`) BETWEEN :hour_start AND :hour_end AND WEEKDAY(`c_time`) IN ($days)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':start_date', $start_date, PDO::PARAM_STR);
$stmt->bindParam(':end_date', $end_date, PDO::PARAM_STR);
$stmt->bindParam(':hour_start', $hour_start, PDO::PARAM_STR);
$stmt->bindParam(':hour_end', $hour_end, PDO::PARAM_STR);
foreach ($days as $key => &$val) {
$stmt->bindParam($key, $val);
}
$stmt->execute();
It works well.
The problem is that if a user selects filters criteria that gives 0 results, the script gives an error:
Warning: Invalid argument supplied for foreach()
Its the foreach loop in the query.
Is there a way to change the “path” of code execution so that I can echo a message saying that the filtering criteria gave 0 results and when the user clicks OK the code returns to the form for a new attempt ?
Here is the array : $days = [0,1,2,3,4,5,6];.
Its an array of ontegers.
It can also be [1,4]
You gave some remarks about it in my previous topic.
It works well now. The problem asises when I select a time range which generates 0 results in dates range or hours range.
I want somehow to insert a condition that stops the code and echo a message to the user saying that the selection gave 0 results
While the posted code does produce an error at the foreach() loop being shown, the OP is asking about a foreach() loop later in the code, that’s looping over the result from the query, when it matches zero rows.
It only works. By putting the external data directly into the sql query statement (the imploded string in the $days variable), it is not secure and will allow sql injection that will let a hacker include the contents of any of your database tables in the output from this query. The main point of using a prepared query is to prevent sql special characters in data from breaking the sql query syntax, which is how sql injection is accomplished. You need to properly build and execute a prepared query.
This is the correct answer to address the problem of the query matching no data (except that the OP is apparently returning a zero, not a boolean or an empty array.)
Conditional logic is fundamental to what programming is about, and lets the program test input values to control what to do next.
The following is list of the OPs threads dealing with the weekday part of the query -
How do I filter results when a variable is an array? (This one shows how the OP is/was fetching the data. You should just return the fetchAll() result. It is an empty array if the query didn’t match any data. Using count() on the value will both tell you how many rows, in case you want to display that value, and let you test if you should loop over the data to produce the output.)
Well, the OP disagrees with your description of the OP?
@erezvol, the only way the foreach you have shown in your OP throws an error that begins “Warning: Invalid argument supplied for foreach()” is that $days is not an array.
If the error message is NOT pointing at that foreach, then you need to show us the CORRECT line of code that is throwing the error.
If it IS pointing at that foreach, then your array of integers isnt an array of integers and you need to figure out why.
Regardless, your query will never work with $days being an array of integers when it gets to the start of the text block as given, because what your query will read as is
INSERT INTO filtered (id, username, ticket, o_time, type, size, item, o_price, s_l, t_p, c_time, c_price, profit) SELECT * FROM data WHERE c_time BETWEEN :start_date AND :end_date AND TIME(c_time) BETWEEN :hour_start AND :hour_end AND WEEKDAY(c_time) IN (Array)
This will generate an error because the SQL database cannot understand what “IN (Array)” means, because Array is not a column name in the table. The implicit conversion of an array to string is the string “Array”.
foreach ($days as $key => &$val) {
$stmt->bindParam($key, $val);
}
What am I missing? There are no parameters in the query called “0”, “1”, and so on, which are what I believe the values of the $key variable will be when you foreach through the $days array in the format shown.
And why is that & in the first line of it? I know it’s a pointer to a variable, I just don’t see why it’s needed here. (ETA - I just tried it with and without, it makes no difference).
I suspect that the array has been imploded into a string for insertion into the query. That of course will cause the foreach to fail as described, while the query will run.
If $days were an array, the query would fail and the foreach would run (if the script gets that far).
I guess that was an unfinished attempt at binding the IN parameters for a proper prepared statement. But to work it would need the proper placeholders in the query, not the array/string used.
Before using an array for the first time, always set it up first as an empty array: eg:
$days = array();
That sets it up as an array, so that any code that is expected to work with it, receives what it’s expecting to be working with, an array, no matter how many entries (if any) are in the array.