clickable mysql generated lists

[chrisj8221]

I'm sure another super newbie question.

I have a page where I am populating a list from a mysql database table with a variable passed from a previous page.

code snippet:

// set varibles
$company = ($_POST['cds_user_company']);
$pass = ($_POST['cds_passtext']);

// Query the database
$query_plist = "SELECT * FROM cds_projects WHERE cds_company - $company ORDER BY cds_proj_name DESC";
$result_plist = mysql_query ($query_plist) or die ("query_plist on project_list.php failed to run"); // Run the query.

echo '<table width="750" border="0" align="center" cellpadding="0" cellspacing="0">
	<tr> 
		<td width="205" valign="top">';
echo '<br><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Select Project :</br></br>';

// create project listing loop
if ($result_plist) { // If it ran OK, display the records.

// Fetch and print all the records.
	while ($row = mysql_fetch_array($result_plist, MYSQL_ASSOC)) {
		echo '<font size="1" face="Verdana, Arial, Helvetica, sans-serif">
		' . $row['cds_proj_name'] . '</font></br>';
	}

mysql_free_result ($result_2); // Free up the resources.

This list generates the way it is suppose to.

I am wanting to make this list clickable so that if a person clicks on one of the values, it is passed onto another SELECT statement on the same page.

so if the above php generates something like below...

project 1
project 2
project 3
project 4

and the user clicks on line

project 4 there is a value assigned to the click taken from a mysql database table, from record row cds_proj_name (value may be ... project 4)

The value is passed onto a select statement like below in order to create another mysql list

$query_pentry = "SELECT * FROM cds_proj_logs WHERE cds_proj_name = $cds_proj_name
ORDER BY cds_log_created DESC";
	$result_pentry = mysql_query ($query_pentry) or die ("query_pentry failed to run"); // Run the query.

My question is I am not sure how to handle this request - it is a function, some sort of hyperlink, or what?

Any suggestions would be greatly appreciated.

Thanks in advance

chris

[devinemke]

the basic idea:

<?php
// connect to db here

if (isset($_POST['cds_user_company']))
{
	$result = mysql_query("SELECT * FROM cds_projects WHERE cds_company = '" . $_POST['cds_user_company'] . "' ORDER BY cds_proj_name DESC") or exit(mysql_error());
	while ($row = mysql_fetch_assoc($result))
	{
		echo '<a href="' . $_SERVER['PHP_SELF'] . '?cds_proj_name=' . urlencode($row['cds_proj_name']) . '">' . $row['cds_proj_name'] . '</a></br>';
	}
}

if (isset($_GET['cds_proj_name']))
{
	$result = mysql_query("SELECT * FROM cds_proj_logs WHERE cds_proj_name = '" . $_GET['cds_proj_name'] . "' ORDER BY cds_log_created DESC") or exit(mysql_error());
	echo '<pre>';
	while ($row = mysql_fetch_assoc($result))
	{
		echo print_r($row) . "\n\n";
	}
	echo '</pre>';
}
?>

[chrisj8221]

thanks for the quick reply - will have to try that.

[Jack_McSlay]

devinemke wrote:

<?php (...)
	$result = mysql_query("SELECT * FROM cds_projects WHERE cds_company = '" . $_POST['cds_user_company'] . "' ORDER BY cds_proj_name DESC") or exit(mysql_error());(...)

oh no! SQL injection detected! ok it's harder to SQL-inject $_POST, but it's better not to risk it

<?php (...)
	$result = mysql_query("SELECT * FROM cds_projects WHERE cds_company = '" . mysql_real_escape_string ($_POST['cds_user_company']) . "' ORDER BY cds_proj_name DESC") or exit(mysql_error());(...)

[chrisj8221]

this is what a noob I am. I was reading jack's last line of code, trying to fiqure out how the where statement was being used and then realized it was a signature.

Good thing I was not up all night trying to fiqure that one out.

chris

[chrisj8221]

i coded the following as suggested

if (isset($_POST['cds_user_company'])) 
{ 
	$result = mysql_query("SELECT * FROM cds_projects WHERE cds_user_company = '" . mysql_real_escape_string 
		($_POST['cds_user_company']) . "' ORDER BY cds_proj_name DESC") or exit(mysql_error());
    while ($row = mysql_fetch_assoc($result)) 
    { 
        echo '<a href="' . $_SERVER['PHP_SELF'] . '?cds_proj_name=' . urlencode($row['cds_proj_name']) . '">' . $row['cds_proj_name'] . '</a></br>'; 
    } 
} 

and a nice list of projects for the client came up with visual hyperlink formatting.

I understand the isset idea - run code only if cds_use_company has something in it.

The querry gives me a list of projects from table 'cds_projects' for the previously choose company.

Here is where I get a little lost... what does the . mysql_real_escape_string specifically do? and what is the variable that I will need to pass on to my next SELECT string so that when a user clicks on a project generated by this list, another table is reiewed and only log enties for that client and project is shown?

I'm trying to understand what I am coding - not just getting help, entering code, and moving on.

Thanks for the help again.

chris