Jump to content

PHP FIND_IN_SET() not works


Recommended Posts

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 by amirelgohary1990
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.