Eddie Eddie - 2 months ago 14
MySQL Question

mysql query array counter

Apologies if I have the terminology wrong.

I have a for loop in php which operates a mysql query...

for ($i = 0; $i <count($user_id_pc); $i++)
{
$query2 = " SELECT job_title, job_info FROM job_description WHERE postcode_ss = '$user_id_pc[$i]'";

$job_data = mysqli_query($dbc, $query2);

$job_results = array();

while ($row = mysqli_fetch_array($job_data))
{
array_push($job_results, $row);
}

}


The results that are given when I insert a...

print_r ($job_results);



On screen -> Array()


If I change the query from
$user_id_pc[$i]
to
$user_id_pc[14]
for example I receive one set of results.

If I include this code after the query and inside the for loop

echo $i;
echo $user_id_pc[$i] . "<br>";


I receive the number the counter
$i
is on followed by the data inside the array for that counter position.

I am not sure why the array
$job_results
is empty from the query using the counter
$i
but not if I enter the number manually?

Is it a special character I need to escape?

The full code

<?php
print_r ($user_id_pc);

//Select all columns to see if user has a profile
$query = "SELECT * FROM user_profile WHERE user_id = '" . $_SESSION['user_id'] . "'";

//If the user has an empty profile direct them to the home page

$data = mysqli_query($dbc, $query);

if (mysqli_num_rows($data) == 0)
{
echo '<br><div class="alert alert-warning" role="alert"><h3>Your appear not to be logged on please visit the<a href="index.php"> home page</a> to log on or register. <em>Thank you.</em></h3></div>';
}
//Select data from user and asign them to variables
else
{
$data = mysqli_query($dbc, $query);
if (mysqli_num_rows($data) == 1)
{
$row = mysqli_fetch_array($data);
$cw_job_name = $row['job_description'];
$cw_rate = $row['hourly_rate'];
$job_mileage = $row['mileage'];
$job_postcode = $row['postcode'];
$response_id = $row['user_profile_id'];
}
}


for ($i = 0; $i <count($user_id_pc); $i++)
{
$query2 = " SELECT job_title, job_info FROM job_description WHERE postcode_ss = '{$user_id_pc[$i]}'";
$job_data = mysqli_query($dbc, $query2);
$job_results = array();
while ($row = mysqli_fetch_array($job_data))
{
array_push($job_results, $row);
}

echo $i;
?>
<br>
<?php

}
print ($query2);
print $user_id_pc[$i];
?>

Answer

This is primarily a syntax error, the correct syntax should be:

$query2 = " SELECT job_title, job_info FROM job_description WHERE  postcode_ss = '{$user_id_pc[$i]}'";

Note that this is correct syntax but still wrong!! For two reasons the first is that it's almost always better (faster, more efficient, takes less resources) to do a join or a subquery or a simple IN(array) type query rather than to loop and query multiple times.

The second issue is that passing parameters in this manner leave your vulnerable to sql injection. You should use prepared statements.

The correct way

if(count($user_id_pc)) {
    $stmt = mysqli_stmt_prepare(" SELECT job_title, job_info FROM job_description WHERE  postcode_ss = ?");
    mysqli_stmt_bind_param($stmt, "s", "'" . implode("','",$user_id_pc) . "'");
    mysqli_stmt_execute($stmt);
}

Note that the for loop has been replaced by a simple if