Pass variable to MySQL on Checkbox state

[The_FedEx_Guy]

I have a page that brings up information in a table for people who need to pay for the work carried out for them.

I have an invoice text link going to invoice.php and then the value of the "job_id"
My boss has said now that he needs to be able to add other jobs for that customer in the invoice.

So if that customer has 4 jobs and he wants to make an invoice for 3 jobs then he wants a checkbox to click on every row that he wants to add.

My Problem is I do not know how to do this and pass the same value (job_id) to the invoice.php

I have two pages the one that lists the information is previous_jobs.php and the other is invoice.php

[The_FedEx_Guy]

do you think it will work if I have some kind of loop?

[Newsgroup_User]

The_FedEx_Guy wrote:
> if that customer has 4 jobs and he wants to make an invoice for 3 jobs then
> he wants a checkbox to click on every row that he wants to add.
>
> My Problem is I do not know how to do this and pass the same value (job_id) to
> the invoice.php

The checkboxes need to have the same name, and their values passed as an
array to the next page.

Change this:

> <label>
> <input name="add" type="checkbox" id="add" value="Yes" />
> </label>

to this:

<label>
<input name="add[]" type="checkbox" id="add_<?php echo $ac_sw1; ?>"
value="<?php echo $row_jobs['job_id']; ?>" />
</label>

The square brackets after the name turn "add" into an array. I have also
used your row counter, $ac_sw1, to create a unique ID for each checkbox.
Finally, the value of each checkbox is set to its job_id.

You also need to wrap the checkboxes in a form, and use a submit button
to send the selection to the next page.

In the next page, you can extract the values from $_POST['add'] and use
them in a SQL query like this:

if (isset($_POST['add']) && is_array($_POST['add'])) {
$OK = true;
// make sure only numbers are included in the array
foreach ($_POST['add'] as $val) {
if (!is_numeric($val)) {
$OK = false;
}
}
// flatten array
if ($OK) {
$jobs = implode(',', $_POST['add']);
} else {
$jobs = 0;
}
}

You can then use $jobs safely in a SQL query:

$sql = "SELECT * FROM jobs WHERE job_id IN($jobs)";

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS4",
"PHP Solutions" & "PHP Object-Oriented Solutions"
http://foundationphp.com/

[The_FedEx_Guy]

Hi David,
What does IN($jobs) do?
I have given this code a try but it comes up with none of the jobs listed.

I've had to put single quotes to your IN($jobs) bit as it came up with an error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LIMIT 0, 50' at line 1


mysql_select_db($database_db, $db);
$query_jobs = "SELECT * FROM jobs LEFT JOIN customer ON customer.cust_business_name = jobs.cust_business_name WHERE job_id IN('$jobs')";
$query_limit_jobs = sprintf("%s LIMIT %d, %d", $query_jobs, $startRow_jobs, $maxRows_jobs);
$jobs = mysql_query($query_limit_jobs, $db) or die(mysql_error());
$row_jobs = mysql_fetch_assoc($jobs);

//
//mysql_select_db($database_db, $db);
$query_jobs1 = "SELECT sum(job_cost) as sum_job_cost FROM jobs LEFT JOIN customer ON customer.cust_business_name = jobs.cust_business_name WHERE job_id = '$_REQUEST[job_id]' ";
$query_limit_jobs1 = sprintf("%s LIMIT %d, %d", $query_jobs1, $startRow_jobs, $maxRows_jobs);
$jobs1 = mysql_query($query_limit_jobs1, $db) or die(mysql_error());
$row_jobs1 = mysql_fetch_assoc($jobs1);

[Newsgroup_User]

The_FedEx_Guy wrote:
> Hi David,
> What does IN($jobs) do?

IN() is a MySQL function that checks whether a value is in a list of values:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

> I have given this code a try but it comes up with none of the jobs listed.
>
> I've had to put single quotes to your IN($jobs) bit as it came up with an
> error:

As you will see from the MySQL reference manual, the single quotes
should not be there. Use echo to see what the SQL query actually
contains when you test it.

Doing a quick test on one of my databases, the following query works
perfectly:

SELECT * FROM gardens WHERE garID IN(4,5,8) LIMIT 0,10

It results in the retrieval of three records (garID 4, garID5, and garID
8).

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS4",
"PHP Solutions" & "PHP Object-Oriented Solutions"
http://foundationphp.com/

[The_FedEx_Guy]

Hi David,
I'm still getting that problem "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LIMIT 0, 50' at line 1" no matter what I try to get it working.

I have attached my invoice.php code

[Newsgroup_User]

The_FedEx_Guy wrote:
> Hi David,
> I'm still getting that problem "You have an error in your SQL syntax; check
> the manual that corresponds to your MySQL server version for the right syntax
> to use near ') LIMIT 0, 50' at line 1" no matter what I try to get it working.
>
> I have attached my invoice.php code

Sorry, I don't have time to trawl through hundreds of lines of code. The
answer to your problem lies in studying what the SQL query actually
looks like when it's submitted to MySQL. You can do that by using echo
to display the query. Once you see the contents of the query, tracking
down the cause of the error becomes infinitely easier.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS4",
"PHP Solutions" & "PHP Object-Oriented Solutions"
http://foundationphp.com/