[SOLVED] Help Required MYSQL Query Please

[mrbigdog]

Hi,

 

Hoping someone can help me. I have looked on the net for tutorials and examples but have found nothing...

 

Basically, I have a table for trainees details, and then two other tables for logging those trainees though training on different devices (t545, t34)

 

The form before my details page needs to get the trainees name and details for those trainees in either the t545 or the t34 table.

 

How do I get my mysql recordset to choose data from the t545 or the t34 table depending on what the user selects from the drop down menu?

 

Eg.

 

SELECT cust_id, user_id, user_firstname, user_surname

FROM trainees, tablename

WHERE tablename.cust_id = trainees.cust_id

 

The 'tablename' variable would be ($_GET['product_name']) from the previous form on the previous page...

 

Any ideas how I can do this? Help appreciated. Hope i have explained it well enough.

 

Paul.

[revraz]

 

 

$tablename = $_GET['product_name'];

 

$sql = "SELECT cust_id, user_id, user_firstname, user_surname

FROM trainees, tablename

WHERE '$tablename'.cust_id = trainees.cust_id";

 

[mrbigdog]

Hi,

 

I have used he following code in the recordset:

 

SELECT trainees.user_id, user_firstname, user_surname, user_designation, user_dept

FROM trainees, tablename

WHERE `tablename`.user_id = colname AND trainees.user_id = colname

 

colname = $_GET['cust_id']

tablename = $_GET['product_name']

 

I get the following error when i get to the details page:

 

Table 'mckinl3.'t34'' doesn't exist

 

any suggestions?

[revraz]

$colname = $_GET['cust_id'];
$tablename = $_GET['product_name'];

$sql="SELECT trainees.user_id, user_firstname, user_surname, user_designation, user_dept
FROM trainees, '$tablename'
WHERE '$tablename'.user_id = '$colname' AND trainees.user_id = '$colname'";
$result=mysql_query($sql) or die(mysql_error());

[mrbigdog]

Hi Revraz,

 

Thanks for that. However, still cant get it to work. I'm using dreamweaver, so the sql code is going into a recorset (which normally works fine) - not as an actual $sql query into the php. Every alternative I try gives me a msql syntax error - especially if i use single quotes around 'tablename'.

 

The only way it will vaguely work is if i enclose `tablename` in the forward slash type of quotes. Then I get the error from the post above.

 

I have tried pasting the code you wrote for me into the php script instead, but get a mysql syntax error.

 

The page, if you want to have a look is :http//www.mckinleymed.co.uk/online-training/Admin/training_lookup.php

 

You will have to log in as ( username: phampson@interactive-tutors.co.uk , Password: arnold ).

 

Any other ideas?

 

And thanks for your time...

 

Paul.

 

 

[mrbigdog]

hhhheeeeeellllllpppppp!

[noister]

try don't put a single quote ('') and this (``)...

[Barand]

Column or table names only need `...` if they contain spaces or if they have the same name as a mysql reserved word. They never have '...', those are for string literals.

[mrbigdog]

Thanks very much guys. The problem is now fixed using the following code:

 

<?php

 

$table = $_GET['product_name'];

$customer = $_GET['cust_id'];

 

mysql_connect("mysql-3.db.vi.net", "mckinl3", "password") or die(mysql_error());

mysql_select_db("mckinl3") or die(mysql_error());

 

 

$query = mysql_query("SELECT * FROM trainees, `$table` WHERE trainees.cust_id = $customer AND `$table`.user_id = trainees.user_id");

while($row = mysql_fetch_array($query, MYSQL_ASSOC)) {

 

foreach($row as $k=>$v)

$$k = $v;

 

 

?>

 

   

        <td width="26%" class="normal_caps"><? echo $user_firstname; ?> <? echo $user_surname; ?></td>

        <td width="21%" class="normal_caps"><? echo $user_designation; ?></td>

        <td width="18%" class="normal_caps"><? echo $user_dept; ?></td>

        <td width="35%" class="normal_caps">EDIT<br />

        DELETE</td>

      </tr> <?

++$i;

}

echo "";

 

?>

    </table>

  </div>

 

Easy when you know how!

 

Thanks Paul.