PHP FIND_IN_SET() not works

[amirelgohary1990]

I tried to use FIND_IN_SET() with prepared statement, but did work, do not return any result, or even errors

if(escape($_POST['jobCategory']) != "all-categories" && escape($_POST['countryId']) == "all-countries" && escape($_POST['careerLevel']) == "all-career-levels"):

$the_array = [77,181];
$job_id_imploded = implode(',',$the_array);
$query = mysqli_prepare($dbConnection,"SELECT jobs.id, jobs.job_title, jobs.country_id, employers.employer_name FROM jobs LEFT JOIN employers ON jobs.employer_id = employers.employer_id WHERE job_status = ? AND FIND_IN_SET('id',?)");

mysqli_stmt_bind_param($query,'si',$job_status,$job_id_imploded);

endif;

mysqli_stmt_execute($query);
mysqli_stmt_bind_result($query,$job_id,$job_title,$countryId,$employer_name);
while(mysqli_stmt_fetch($query)){

?>

<div class="job-title">
<a href="job_post.php?job_id=<?php echo htmlspecialchars($job_id) ?>" class="job-title-link"><?php echo htmlspecialchars($job_title); ?></a>
</div>

<?php } // End While ?>

 

Edited July 24, 2023 by amirelgohary1990

[ginerjm]

It "did work"?  Or it "did not work"?

do you have error checking enabled for your script?

[amirelgohary1990]

6 minutes ago, ginerjm said:

It "did work"?  Or it "did not work"?

do you have error checking enabled for your script?

Code not returning anything

Yes, I enabled errors, also no errors, but I am sure something wrong, when I switch to normal query everything works normally with IN()
 

ini_set('display_errors', 1);

ini_set('display_startup_errors', 1);

error_reporting(E_ALL);

 

[mac_gyver]

the 1st parameter in FIND_IN_SET() would be the column you are trying to match, e.g. FIND_IN_SET(id,?) (no quotes around the name)

the 2nd parameter is the set to search in. it is a (string) consisting of a comma separated list of values. you would just implode() the array of values using a comma, and supply that as the prepared query input value.

if the job status and/or the array of ids are statically defined in your code, and don't come from external, unknown, dynamic values, why are you going through the trouble of using a prepared query? just put the static values in the sql query statement.

[ginerjm]

If you wrote your code to do some error checking on the success of your function calls there should be some messages for you.

[amirelgohary1990]

13 minutes ago, mac_gyver said:

1- FIND_IN_SET(id,?) (no quotes around the name)

2- the 2nd parameter is the set to search in. it is a (string) consisting of a comma separated list of values. you would just implode() the array of values using a comma, and supply that as the prepared query input value.

3- if the job status and/or the array of ids are statically defined in your code, and don't come from external, unknown, dynamic values, why are you going through the trouble of using a prepared query? just put the static values in the sql query statement.

1- Noted, and edited

2- I think this is the last point I need, I added the imploded ids inside mysqli_stmt_bind_param, is this right, or I have to put somewhere else,I tried even to put my integers manually inside the FIND_IN_SET('id',77,181), but did not work gives me error Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, bool given 

3- Actually $the_array = [77,181]; I wrote here as static, but in my project this array comes from another dynamic query, that comes from the website when user search for specific country,
Regarding 

[ginerjm]

As MacGyver said - the values need to be expressed as a string, not an array and not an extra parameter as you just did.

Try:

find_in_set(id, '77,181')

And drop the quotes on the column name as you were also told.

[mac_gyver]

4 minutes ago, amirelgohary1990 said:

this array comes from another dynamic query

you would use one single JOIN query.

[mac_gyver]

56 minutes ago, amirelgohary1990 said:

2- I think this is the last point I need, I added the imploded ids inside mysqli_stmt_bind_param, is this right, or I have to put somewhere else,I tried even to put my integers manually inside the FIND_IN_SET('id',77,181), but did not work gives me error Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, bool given 

this is a follow-on error, because the prepare() call failed, and you don't have any error handling to detect and stop execution upon an error.

you always need error handling for statements that can fail. for database statements that can fail - connection, query, exec, prepare, and execute, the simplest way of adding error handling, without adding code at each statement, is to use exceptions for errors and in most cases simply let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) the exception to this is when inserting/updating duplicate or out of range user submitted data. in this case, you would catch the exception, detect if the error number is for something your code is designed to handle, then setup a unique and helpful message for the user letting them know what was wrong with the data that they submitted. for all other error numbers, just re-throw the exception and let php handle it. to enable exceptions for errors for the mysqli extension, add the following line of code before the point where you make the database connection (note: this is the default setting now in php8+) -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);