posting db query to html table

[diamondlifestyle]

hi, I'm working with a script I've written (with a *lot* of help!!) I'm trying to get the results of a db search to be displayed in a html table, with a row for each result. I'm almost there, I've got 1 glitch and 1 cosmetic issue I can't resolve with the below script, any help would be greatly appreciated!!

 

1. the table displays the entire contents of the db before it is filtered through the search, I think this has something to do with the $num=mysql_numrows($result); expression, but I'm not sure how to fix it

 

2. I'd like the last column of the table to be about twice as wide as the others, as it contains a lot of free text, would I have to set the length of each column in order to do this or is there a shorthand way?

 

the current script is:

 

<form method="post" name="Search" action="test2.php">

<input type="text" name="name" autocomplete="OFF" />

<input value="Search" type="submit" name="Search" />

<input value="yes" type="hidden" name="submitted" />

</form>

 

 

<?php

if($_POST['submitted'] == 'yes')

 

$username="*****";

$password="*****";

$database="*****";

$server="localhost";

$db_handle = mysql_connect($server, $user_name, $password);

$db_found = mysql_select_db($database, $db_handle) or die( "Unable to select database");

 

 

$query="SELECT * FROM ***** WHERE surname LIKE '" . mysql_real_escape_string($_POST['name']) . "%'";

$result=mysql_query($query) or die ('<br>Query string: ' . $SQL . '<br>Produced error: ' . mysql_error() . '<br>');

 

if (mysql_num_rows($result) == 0) {

echo "No results found";

exit;

}

 

$num=mysql_numrows($result);

 

 

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";

echo "<table border='1'><tr>";

for($i=0; $i<$fields_num; $i++)

{

    $field = mysql_fetch_field($result);

    echo "<td>{$field->name}</td>";

}

echo "</tr>\n";

 

while($row = mysql_fetch_row($result)){

    echo "<tr>";

foreach($row as $cell)

        echo "<td>$cell</td>";

    echo "</tr>\n";

}

mysql_free_result($result);

mysql_close($db_handle);

?>

[joel24]

1. the table displays the entire contents of the db before it is filtered through the search, I think this has something to do with the $num=mysql_numrows($result); expression, but I'm not sure how to fix it

% is a wildcard in mysql queries, i.e. WHERE surname LIKE '%a%' will search for anything with a in it. Whilst WHERE surname LIKE 'a%' (like in your code) will search for anything starting with the letter a. In your code you have where SURNAME like '$_POST['name']%'... searching for anything that starts with the form posted surname.

 

Try put this bit of code underneath the $query= line and then you'll be able to see all the values posted from the form and the mysql search string you are generating and then diagnose the problem.

print_r($_POST);

echo $query;

exit();

 

2. I'd like the last column of the table to be about twice as wide as the others, as it contains a lot of free text, would I have to set the length of each column in order to do this or is there a shorthand way?

set the width of the last column using CSS and the others will still have automatic widths set. Shorthand way, you only need to set the width of one cell in the column and all others will follow suit.

[DavidAM]

1. the table displays the entire contents of the db before it is filtered through the search, I think this has something to do with the $num=mysql_numrows($result); expression, but I'm not sure how to fix it

 

Unless this is a typo, you did not include the code to be executed in braces to be controlled by the IF statement.  So, the only thing the IF is controlling is the assignment of $username.

<?php
if($_POST['submitted'] == 'yes')

$username="*****";

 

Should be:

<?php
if($_POST['submitted'] == 'yes') {

$username="*****";

// REST OF YOUR CODE HERE

}