Post Multiple Values From Select Value

[fbm247]

Hi,

 

I'm farily new to PHP and trying to run before i can walk but a current project I am working on is a mini task tracker for customer work.

 

I have 2 tables in my DB, customers and tasks.

 

I have pages setup to enter customers into the DB and this all works perfectly.

 

What i'm trying to do on a task creation page is list my customers from my customer DB table into a select field on my New Task page so that it saves the task against the customer it is for.

 

This what i got so far

 

<select class="form_select" name="customervalue">
<?php
print_dbconn();
$result = mysql_query("SELECT * FROM `customers`")
or die(mysql_error());
echo "<option></option>";
while($row = mysql_fetch_array($result))
{
echo "<option value='".$row['cid'].",".$row['name']."'>".$row['name']."</option>";
}
?>
</select>

 

This generates the following in my page

<select class="form_select" name="customervalue">
<option></option>
<option value='1001,Joe Bloggs'>Joe Bloggs</option>
<option value='1002,John Doe'>John Doe</option>
</select>

 

Great it's putting the customer ID and the Customer name into the value field. Now I want to save these 2 values into my task table when I save the task i am creating.

 

The select name is "customervalue" as i was looking at using explode or something on the save page but my knowldge doesn't go that far so this is as far as i got.

 

I managed to get it saving the customer ID only with a slightly modifed version of the above but this is not good enough becuase this is just numbers and as the number of customers and tasks grow i wont recognise which customer id is which customer so logically want to display the customer name as part of the task page at the same tiem as keepign the customer id on the task page aswell so i can do filtering other reports based on the customer ID.

 

Any help much appreciated,Thanks in advance

[fbm247]

SOrry forgot to mention the form i'm working in is then posted to a save.php file which just $_POST all values into variables and then inserts them into the tasks table

[Muddy_Funster]

storing just the customer ID in the tasks table is fine - actualy it's the correct way to do it. You just cross refference the customer table when you are displaying the tasks. ie

SELECT customers.customerID,
customers.customerName,
tasks.taskID,
tasks.taskName,
tasks.Imprtoance
FROM customers
INNER JOIN tasks
ON(customers.customerID = tasks.customerID)
WHERE tasks.complete = 0 and customers.customerID = $currentCustID

 

Edt: elaboration

Edited December 19, 2012 by Muddy_Funster

[fbm247]

hey muddy thanks for that sample code im going to try and work through it and work it into my code.

 

this is my task overview table which displays my tasks in a table so need to try and work out how to get your code into this

 

<table width="700px" border="0" cellspacing="0" cellpadding="0">
   <?php
   print_dbconn();
   $result = mysql_query("SELECT * FROM `cms_tasks` ORDER BY `taskid` DESC")
   or die(mysql_error());
   $num_rows = mysql_num_rows($result);
  if($num_rows == '0'){
  echo "<tr style='background-color:#f6fabd;'><td colspan='5'><p align='center'>Oops, it appears you don't have any tasks created yet!</p></td></tr>";
  }
   while($row = mysql_fetch_array($result)){
   ?>
	  <tr>
	   <td class="taskhead" width="120"><b>TASK ID: <?php echo $row['taskid'];?></b></td>
	    <td class="taskhead" width="260"><b>Customer Name Here</b></td>
	    <td class="taskhead" width="150"><b>ETA: <?php echo $row['eta'];?></b></td>
	    <td class="taskhead" width="120"><a class="edit" href="?module=/tasks/edit&taskid=<?php echo $row['taskid'];?>">edit</a><a class="delete" href="?module=/tasks/delete&taskid=<?php echo $row['taskid'];?>" onclick="return confirm('Are you sure you want to delete this task?');">delete</a></td>
	  </tr>
	  <tr>
	    <td colspan="4" style="border-bottom:solid 1px #666666; line-height:16px; color:#333333; padding:10px"><?php echo limit($row['description']); ?></td>
	  </tr>
   <?php
   }
   ?>
	  </table>

[Muddy_Funster]

that's not your table, that's the code you are currently using to show your tasks on the page. what's the actual table structure for the customer and task tables?

[fbm247]

I meant it's my html table im using to get the results on my task overview page

 

my table structure is pretty much

 

customer table

cid

name

email

phone

address1

address2

town

county

postcode

status

 

task table

taskid

cid

customer

description

notes

eta

taskstatus

[Muddy_Funster]

so you would want to replace your

$result = mysql_query("SELECT * FROM `cms_tasks` ORDER BY `taskid` DESC")

 

query with the following:

$sql = <<<TASK_SQL
SELECT cms_tasks.cid,
 name,
 taskid,
 description,
 notes,
 eta
FROM cms_tasks
INNER JOIN customers
 ON(cms_tasks.cid = customers.cid)
WHERE taskstatus != 'closed'
ORDER BY taskid DESC
TASK_SQL;
$result = mysql_query($sql) or die (mysql_error());
...
while ($row = mysql_fetch_assoc($result)){
//$row array will have the following keys :
//$row['cid']
//$row['name']
//$row['taskid']
//$row['description']
//$row['notes']
//$row['eta']
//use them in your table the same as you are doing with the other values, you don't even need to show the cid if you don't want, in which case just remove it from the select
...
}

[Psycho]

Here is a rewrite of your code to output the tasks. The only thing you did not provide was the name of the customer's table - so change that as needed in the query below. I also broke up the code to separate the logic from the presentation. This makes maintenance a whole lot easier. Put all of the code to generate the output at the top of your script (or even in a separate file) and then put the output at the bottom of the script using just echo statements.

 

<?php

print_dbconn();

$query = "SELECT c.name,
			 t.taskid, t.eta, t.description
	  FROM customers AS c
	  LEFT JOIN `cms_tasks` AS t
	    ON c.cid = t.cid
	  ORDER BY `taskid` DESC";
$result = mysql_query($query) or die("Query: <$query<br>Error: ". mysql_error());

$taskOutput = '';
if(!mysql_num_rows($result))
{
   $taskOutput .=  "<tr style='background-color:#f6fabd;'>\n";
   $taskOutput .=  "<td colspan='4'><p align='center'>Oops, it appears you don't have any tasks created yet!</p></td>\n";
   $taskOutput .=  "</tr>";
}
else
{
   while($row = mysql_fetch_array($result))
   {
    $description = limit($row['description']);
    $tasksOutput .= "<tr>\n"
    $tasksOutput .= "    <td class='taskhead' width='120'><b>TASK ID: {$row['taskid']}</b></td>\n";
    $tasksOutput .= "    <td class='taskhead' width='260'><b>{$row['name']}</td>\n";
    $tasksOutput .= "    <td class='taskhead' width='150'><b>ETA: {$row['eta']}</b></td>\n";
    $tasksOutput .= "    <td class='taskhead' width='120'>\n";
    $tasksOutput .=	 "<a class='edit' href='?module=/tasks/edit&taskid={$row['taskid']}'>edit</a>\n";
    $tasksOutput .=	 "<a class='delete' href='?module=/tasks/delete&taskid={$row['taskid']}' onclick='return confirm('Are you sure you want to delete this task?');'>delete</a>\n";
    $tasksOutput .= "    </td>\n";
    $tasksOutput .= "</tr>\n";
    $tasksOutput .= "<tr>\n";
    $tasksOutput .= "    <td colspan='4' style='border-bottom:solid 1px #666666; line-height:16px; color:#333333; padding:10px'>{$description}</td>\n";
    $tasksOutput .= "</tr>\n";
    }
}
?>

<table width="700px" border="0" cellspacing="0" cellpadding="0">
<?php echo $tasksOutput; ?>
</table>

[Muddy_Funster]

Damn you Psycho! You always have to go that one step further! At least the Gods buggered your code formatting this time to show their displeasure!

 

(j/k by the way)

[fbm247]

now i have options lol thanks to you both i got it working after playing around with both versions of your code so I could learn what was happening

[Muddy_Funster]

glad to have helped (evin if Psycho is far better at it than I am....*grumbles*)

gl

[Psycho]

Just to add to what Muddy_Funster first stated. You do not want to be duplicating data across multiple tables (such as customer name). The purpose of a relational database is to relate data across tables. This is typically accomplished by using the ID (or another unique value) from one table as a foreign key in another table of records to relate them. In this case you only want to use the Client ID in the tasks table. You can then reference the client name (or any other data in the client record) by JOINing the records in the two tables. I suggest you look for some database tutorials on database design and how to do JOINs.