Page 1 of 2

Update values from repeated table

Posted: Thu Nov 19, 2009 6:51 am
by koolsamule
Hi Chaps,
I have a Query that pulls data from 3 different job tables:

Code: Select all

tbl_jobs
- jobid (auto) primary key
- jobinvsent (y,n)
tbl_jobxml
- jobid (auto) primary key
- jobinvsent (y,n)
tbl_jobtrans
- jobid (auto) primary key
- jobinvsent (y,n)
I'm able to present the data in an HTML table, but what I need is the ability to update the values of jobinvsent to 'y' of all the jobid's presented.
I'm guessing I'll need to wrap the table in a form and then post it to a script.php file, but I don't know how to gather all the jobid's in such a way that the scrip file will know which table it came from.
Would really appreciate any help!

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 7:10 am
by iankent
koolsamule wrote:Hi Chaps,
I have a Query that pulls data from 3 different job tables:

Code: Select all

tbl_jobs
- jobid (auto) primary key
- jobinvsent (y,n)
tbl_jobxml
- jobid (auto) primary key
- jobinvsent (y,n)
tbl_jobtrans
- jobid (auto) primary key
- jobinvsent (y,n)
I'm able to present the data in an HTML table, but what I need is the ability to update the values of jobinvsent to 'y' of all the jobid's presented.
I'm guessing I'll need to wrap the table in a form and then post it to a script.php file, but I don't know how to gather all the jobid's in such a way that the scrip file will know which table it came from.
Would really appreciate any help!
When you do you're SELECT query to retrieve the jobs (I assuming using a UNION?), create a new column in the result which tells the table name, e.g.

Code: Select all

SELECT jobid, jobname, jobdesc, etc, etc2, etc3, 'tbl_jobs' as table FROM tbl_jobs
UNION
SELECT jobid, jobname, jobdesc, etc, etc2, etc3, 'tbl_jobxml' as table FROM tbl_jobxml
UNION
SELECT jobid, jobname, jobdesc, etc, etc2, etc3, 'tbl_jobtrans' as table FROM tbl_jobtrans
That way you have a column in the result that tells you where that particular result came from.

When you say you need to update the jobinvsent column to Y, do you mean when its displayed, or does the user have to do some additional action (e.g. select a checkbox and click a button)? If as soon as its displayed, there's no need to send the info to the HTML page and submit it back (waste of time), simply run another query afterwards to update the column of any items returned by the first query.

If you want the user to have to do something before it gets marked as sent, then simply send the table name along with the job id to the HTML form, then when the form is submitted back to your PHP script it gives both the table name and job id. Be careful though, escaping the table name won't do anything to prevent SQL injection so you must compare it against a pre-defined list of tables before running any queries on it!

hth :)

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 8:57 am
by koolsamule
Hi Ian,
Thanks for that....
The Query is a UNION, drawing info from 3 different tables, WHERE jobid's with jobinvsent='n'.
The page is displayed, presenting the data in a table.
Once the user clicks a 'MARK AS SENT' button, I need all of the jobs to have the jobinvsent value changed to 'y', therefore no longer showing in the table.
I have changed the Query, so I can now determine which table the job came from, but I now need a script that will gather all the jobid's, the table it came from then set the jobinvsent value to 'y', but I haven't got a clue how to do it.....
If it was 1 job, then it wouldn't be a problem, as the end HTML table has a 'repeat region', with multiple jobid's and tables, I'm stuck!

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 9:33 am
by iankent
Every time you output a job, do something like this:

Code: Select all

<input name="somename[]" value="<?=$table_name?>:<?=$item_id?>" />
obviously replace the variables with the correct ones from the current row being displayed. somename[] can be whatever you want, just make sure its got a [] after it! that makes it an array of items in the HTML form, and PHP gets it as an array

when the form is submitted, you should have a variable called $_POST['somename'] which is an array containing a list of items in the format TableName:RowID

So, you can now loop through that array and explode the value, e.g.

Code: Select all

$allowed_tables = Array('table1','table2','table3'); // to prevent SQL injection
 
foreach($_POST['somename'] as $var) {
    $arr = explode(':', $_POST['somename']);
    if(in_array($arr[0], $allowed_tables)) {
        $table = $arr[0];
        $rowid = $arr[1];
        if(is_numeric($rowid)) {
            // run your SQL query here to update $table where row matches $rowid
        }
    }
}
 
alternatively, you could do it all in a lot less SQL queries by adding the results of the loop to an array, then using sql's WHERE..IN.. syntax to update each table in one SQL query (you'd need an array of the format $arr['table_name'][] = $rowid)

hth

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 10:13 am
by koolsamule
Hi Ian,

Thanks for that, it's a great help, would you mind taking a look at what I've now got?:

Code: Select all

 <?php
$table_name = $row_rsInvPending['fromtable'];
$item_id = $row_rsInvPending['jobid'];
$project_id = $row_rsInvPending['projid'];
?>
<input type="hidden" name="jobinvsent[]" value="<? $table_name; ?>:<? $item_id; ?>:<? $project_id; ?>" />
<?php } while ($row_rsInvPending = mysql_fetch_assoc($rsInvPending)); ?>

Code: Select all

$allowed_tables = Array('tbl_jobs','tbl_jobtransline','tbl_jobxml'); // to prevent SQL injection
 
foreach($_POST['jobinvent'] as $var) {
    $arr = explode(':', $_POST['jobinvsent']);
    if(in_array($arr[0], $allowed_tables)) {
        $table = $arr[0];
        $rowid = $arr[1];
        $projid = $arr[2];
        if(is_numeric($rowid)) {
            // run your SQL query here to update $table where row matches $rowid
            $query = sprintf("UPDATE $table SET joninvsent='y' WHERE jobid=$rowid AND FK.projid=$projid");
  $result = mysql_query($query, $conndb2) or die(mysql_error());
        }
    }
}

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 10:18 am
by iankent
This line doesn't seem right:

Code: Select all

$query = sprintf("UPDATE $table SET joninvsent='y' WHERE jobid=$rowid AND FK.projid=$projid");
Where has FK.projid come from? On your table (whichever of the three it is), surely jobid is a unique column? so, for a particular table and jobid there can be only one row, so no need to match against anything else.

Also, in that query, there is no table FK and so you can't use FK.projid in the WHERE part.

Other than that query it all looks good to me!

hth

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 10:21 am
by koolsamule
Hi Ian, sorry, i'm not sure if I mentioned it before, the FK_projid comes from the 'parent' project, where you can have multiple jobs for 1 project, and as there are 3 different tables, with the possibility of the same jobid in more than 1 of the tbl_jobs, tbl_jobtransline and tbl_jobxml tables, I would have to do another check before the record is updated?


Duhh, got what you meant now. . . .$table / $jobid . . . .got it!

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 10:36 am
by koolsamule
Yo Ian, the script doesn't seem to work (the values aren't being updated). . .can you check something else out for me (sorry to be a pain, I'm trying to learn this as I go along)
HTML SOURCE:
...
<input type="hidden" name="jobinvsent[]" value="tbl_jobxml:83" />....
<input type="hidden" name="jobinvsent[]" value="tbl_jobs:122" />
...
Does that look right?

Code: Select all

<?php
$allowed_tables = Array('tbl_jobs','tbl_jobtransline','tbl_jobxml'); // to prevent SQL injection
 
foreach($_POST['jobinvent'] as $var) {
    $arr = explode(':', $_POST['jobinvsent']);
    if(in_array($arr[0], $allowed_tables)) {
        $table = $arr[0];
        $rowid = $arr[1];
        if(is_numeric($rowid)) {
            // run your SQL query here to update $table where row matches $rowid
            $query = sprintf("UPDATE $table SET joninvsent='y' WHERE jobid=$rowid");
  $result = mysql_query($query, $conndb2) or die(mysql_error());
        }
    }
}
?>

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 10:47 am
by iankent
I think this line

Code: Select all

$arr = explode(':', $_POST['jobinvsent']);
should be

Code: Select all

$arr = explode(':', $var);
it was attempting to explode the array rather than the value of that iteration

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 11:08 am
by koolsamule
Hi Ian,
I've tried that, but still not having any luck...the values aren't getting updated.
Is there anything else we can try?
I really do appreciate your help.

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 11:10 am
by iankent
Could you add a print_r($_POST); somewhere and see what that gives you when you submit the form?

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 11:52 am
by koolsamule
Thanks for that, this is what was 'printed':
Array ( [jobinvsent] => Array ( [0] => tbl_jobxml:83 [1] => tbl_jobs:122 ) )
Does that help?

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 11:54 am
by iankent
And I assume you were expecting 2 to be sent? If so then the PHP script is definately getting the values you need.

line 4 of the second bit of code, I think I typed jobinvent instead of jobinvsent, try changing that and see if it works :)

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 12:05 pm
by koolsamule
Hi Ian, I was expecting 2 records, think we're getting somewhere now...changed my typo, re-submitted the form and now it's saying:
No database selected
This is my script.php code:

Code: Select all

<?php require_once('../../Connections/conndb2.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
 
  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
 
  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
$allowed_tables = Array('tbl_jobs','tbl_jobtransline','tbl_jobxml'); // to prevent SQL injection
 
foreach($_POST['jobinvsent'] as $var) {
    $arr = explode(':', $var);
    if(in_array($arr[0], $allowed_tables)) {
        $table = $arr[0];
        $rowid = $arr[1];
        if(is_numeric($rowid)) {
            // run your SQL query here to update $table where row matches $rowid
            $query = sprintf("UPDATE $table SET jobinvsent='y' WHERE jobid=$rowid");
            $result = mysql_query($query, $conndb2) or die(mysql_error());
        }
    }
}
?>
<html>
<body>
<div id="left">
<p><?php print_r($_POST);?></p>
</div>
</body>
</html>
What am I missing???

Re: Update values from repeated table

Posted: Thu Nov 19, 2009 12:13 pm
by iankent
a mysql_select_db() call. just after your mysql_connect() call you need to tell MySQL which database you want to use.