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); ?>

right, I set up a new page (test5) and a new db, and I've moved the script over to the new page and the new db, which only contains 4 entries (2 smiths and 2 jonses) while I test this out. When I search for 'smith' it prints '1' underneath the form (where 'enter surname...' is printed when the screen first loads) and when I search for 'jones' it prints '3'. When I enter a variable I know isn't in the db (e.g. Adams) it doesn't show anything.

changing that just produces the same error, everything looks ok except no results are being displayed.

Hi again, I've been back on this again all morning and something's still going wrong; the page is loading up correctly, displaying the form with the text underneath 'enter surname to continue', however when a query is submitted in the form the page reloads, the text 'enter surname...' vanishes and the page expands down like there's something being displayed underneath the table, but there's nothing there. I'm assuming it's an error with the PRINT function; I've tried a couple of dozen different alterations (which usually just resulted in error messages) and I've also tried changing the WHILE query to an IF query (thinking that as my test query 'smith' isn't the first record it may be stopping the WHILE query before reaching the search results) but that just brings up an ELSE error which I can't seem to fix. I also wondered if the problem was the phrase '$db_field' in the below line if ($db_field = mysql_fetch_assoc($result)) { as it occurred to me that I haven't sepcified what $db_field is anywhere in the script; I've tried to add this on in a couple of different ways: adding $db_field = 'surname' at the top with the other login details amending the above script to if ($db_field ['Surname'] = mysql_fetch_assoc($result)) { but neither of them have worked. I'm really sorry to keep asking for help with this but nothing I'm trying is working. The full script I've got at the minute is: <FORM NAME ="Search" METHOD ="POST" ACTION = "test2.php"> <INPUT TYPE = "TEXT" NAME = "name"> <INPUT TYPE = "Submit" Name = "Search" VALUE = "Search"> <input type="hidden" name="submitted" value="yes"> </FORM> <?php if($_POST['submitted'] == 'yes') { $user_name = "*****"; $password = "*****"; $database = "*****"; $server = "localhost"; $db_handle = mysql_connect($server, $user_name, $password); $db_found = mysql_select_db($database, $db_handle); if ($db_found) { $SQL = "SELECT * FROM ***** WHERE surname LIKE \'" . mysql_real_escape_string($_POST['name']) . "%'\"; $result = mysql_query($SQL) or die ('<br>Query string: ' . $SQL . '<br>Produced error: ' . mysql_error() . '<br>'); while ($db_field = mysql_fetch_assoc($result)) { print $db_field['ID'] . "<BR>"; print $db_field['Grave'] . "<BR>"; print $db_field['Surname'] . "<BR>"; print $db_field['Forenames'] . "<BR>"; print $db_field['Death_Date'] . "<BR>"; print $db_field['Birth_Year'] . "<BR>"; print $db_field['Inscription'] . "<BR>"; } mysql_close($db_handle); } else { print "Database Error "; mysql_close($db_handle); } } else { echo '<br>(Enter a surname to continue)<br>'; } ?> </body> </html>

thank you so much for all your help, that looks like it's going to work (although I have a slight sql error I need to sort out first) but it's now 1.30am where I am so I'm giving up for tonight!! thanks again

I've removed the if() clause completely, this now shows ////////////////// DEBUGGING CODE //////////////////////////////////////////// Array ( ) /////////////////////// END DEBUGGING ///////////////////////////////////////// when I entered a query (surname 'smith' as there are 2 of them in the db) it changed to: ////////////////// DEBUGGING CODE //////////////////////////////////////////// Array ( [surname] => smith ) /////////////////////// END DEBUGGING /////////////////////////////////////////

no I'm just getting what looks like the refreshed page again. no debugging script and still says 'form not submitted' underneath

fixed, thanks. I'm getting nothing but the same screen back when I submit a query. I think it must be an issue with a connection to the db, but am I right in thinking if it was something silly like putting in the wrong password it'd just kick me out and say 'db not available' as per the ELSE query? so I am connected, but the form and the db aren't interacting properly???

sorry for the delay, something very odd has happened, I've loaded the page again to check your last question and it's now saying there's an unexpected } on line 162. I've looked at all the curly brackets and can't see a mistake, I've deleted them all individually and tried it, it still doesn't work, I've deleted the whole script and pasted it from the one you wrote, it did it again, I deleted the page and recreated it, it still does it! this is the only script I'm running on this page while I get it to work. the code I'm using is below, can you see anything? it worked fine the first time except for the not loading thing <html> <head> <title>SEARCH RECORDS</title> </head> <body> <?php if( isset($_POST['Search']) ) { echo '////////////////// DEBUGGING CODE ////////////////////////////////////////////'; echo '<pre>'; print_r($_POST); echo '</pre>'; echo '/////////////////////// END DEBUGGING /////////////////////////////////////////'; } ?> <FORM NAME ="Search" METHOD ="POST" ACTION = "test2.php"> <INPUT TYPE = "TEXT" NAME = "surname"> <INPUT TYPE = "Submit" Name = "Search" VALUE = "Search"> </FORM> <?phpif( isset($_POST['Search']) ) { $user_name = "*****"; $password = "*****"; $database = "*****"; $server = "localhost"; $db_handle = mysql_connect($server, $user_name, $password); $db_found = mysql_select_db($database, $db_handle); if ($db_found) { $SQL = "SELECT * FROM ***** WHERE surname LIKE '" . mysql_real_escape_string($_POST['surname']) . "%'"; $result = mysql_query($SQL) or die ('<br>Query string: ' . $SQL . '<br>Produced error: ' . mysql_error() . '<br>'); echo $SQL; // debugging output while ($db_field = mysql_fetch_assoc($result)) { print $db_field['Grave'] . "<BR>"; print $db_field['Surname'] . "<BR>"; print $db_field['Forenames'] . "<BR>"; print $db_field['Death_Date'] . "<BR>"; print $db_field['Birth_Year'] . "<BR>"; } mysql_close($db_handle); } else { print "Database NOT Found "; mysql_close($db_handle); } } else { echo '<br>(Form not yet submitted.)<br>'; // debugging output } ?> </body> </html>

yup, so it goes on a loop back to itself, is this where I'm getting the error?

Thank you for all your help, I've put it in and it's showing the text (Form not submitted.) underneath the form, when I enter a name and hit submit it's still refreshing the page without displaying results.

sorry, didn't get what you were saying then, I've tested the string in phpmyadmin using the example 'smith' (as I have a couple in the DB), that's coming back as a good script but no results. When I add LIKE to the equation I get a compatability error using LIKE and %, so I'm assuming this is the problem?

I've added the echo, but it's repeating the same error, except now above the table are the words SELECT * FROM ***** WHERE surname='%' Is this possibly an error with the LIKE function? when I write in the script $SQL = "SELECT * FROM ***** WHERE surname LIKE '" . mysql_real_escape_string($_POST['surname']) . "%'"; I get an parse error message about using LIKE with % (I'm using version 5.0.91) but when I omit the like and just use $SQL = "SELECT * FROM ***** WHERE surname '" . mysql_real_escape_string($_POST['surname']) . "%'"; I get the page showing properly but it reloads on itself when I submit a query in the form.

it's not displaying any parse errors now, I've got the page looking exactly as I want it to, except when I submit a query in the form it refreshes the page rather than loads the results of the search. I've tried swapping the code over so the php script is on top and the html table is on the bottom (just in case!) but it hasn't altered anything :-(

ooh just spotted an error on the die phrase (amended in the script above so that I don't have to repost the whole thing again!) now I've got the page with the search box, but it's still refreshing the screen rather than showing results when I press the submit button.

the code currently stands at: <html> <head> <title>SEARCH RECORDS</title> </head> <body> <FORM NAME ="Search" METHOD ="POST" ACTION = "test2.php"> <INPUT TYPE = "TEXT" NAME = "surname"> <INPUT TYPE = "Submit" Name = "Search" VALUE = "Search"> </FORM> </body> </html> <? $user_name = "*****"; $password = "*****"; $database = "*****"; $server = "localhost"; $db_handle = mysql_connect($server, $user_name, $password); $db_found = mysql_select_db($database, $db_handle); if ($db_found) { $SQL = "SELECT * FROM ***** WHERE surname = '" . mysql_real_escape_string($_POST['surname']) . "$'"; $result = mysql_query($SQL) or die ('<br>Query string: ' . $SQL . '<br>Produced error: ' . mysql_error() . '<br>'); while ($db_field = mysql_fetch_assoc($result)) { print $db_field['Grave'] . "<BR>"; print $db_field['Surname'] . "<BR>"; print $db_field['Forenames'] . "<BR>"; print $db_field['Death_Date'] . "<BR>"; print $db_field['Birth_Year'] . "<BR>"; } mysql_close($db_handle); } else { print "Database NOT Found "; mysql_close($db_handle); } ?> which is producing the error 'unexpected T_while on line 190' (however before I added the 'die' phrase it looked right except when the 'submit' button was pressed it refreshed the form, rather than showing the results of the search)

Hi, I pasted the script in

right, that's given me: Parse error: syntax error, unexpected T_WHILE on line177 which has thrown me completely; I thought the 'while' clause was necessary to tabulate the results? I was wondering if it's because the html is above the php, when the 'submit' button is pressed and it sends the data back to the same page, is it hitting the html before the php and just resetting the form? or will that not make any difference? thanks again for everyone's help!

the latest code is: <html> <head> <title>SEARCH RECORDS</title> </head> <body> <FORM NAME ="Search" METHOD ="POST" ACTION = "test2.php"> <INPUT TYPE = "TEXT" VALUE ="surname" NAME = "surname"> <INPUT TYPE = "Submit" Name = "Search" VALUE = "Search"> </FORM> </body> </html> <? $user_name = "*****"; $password = "*****"; $database = "*****"; $server = "localhost"; $db_handle = mysql_connect($server, $user_name, $password); $db_found = mysql_select_db($database, $db_handle); if ($db_found) { $SQL = "SELECT * FROM ***** WHERE surname LIKE '" . mysql_real_escape_string($_POST['surname']) . "%'"; $result = mysql_query($SQL); while ($db_field = mysql_fetch_assoc($result)) { print $db_field['Grave'] . "<BR>"; print $db_field['Surname'] . "<BR>"; print $db_field['Forenames'] . "<BR>"; print $db_field['Death_Date'] . "<BR>"; print $db_field['Birth_Year'] . "<BR>"; } mysql_close($db_handle); } else { print "Database NOT Found "; mysql_close($db_handle); } ?> thank you all for your help with this!!

omg I'm sorry this is so frustrating, now I've got the page, with the form, I type in the name and click 'search' and it refreshes the same page without displaying the results of the search. you're all being very helpful and I really appreciate it...fingers crossed one more tweak might fix it??

thank you so much for your help so far, that looks a lot better now, I've got my page up with the search box in it, but when I type in and press submit I get a HTTP 404 error (page not found/broken). Does this mean I've logged in wrong or something?

omg that returned: Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING on line 176 I don't think I was as close to nailing this as I originally thought! (and it really doesn't like line 176!)

I'm trying to do something that I thought was very simple about 2 weeks ago :-( I want to put a form on my site and link it to a database so when a user types a surname into the form they can search the db and the page will only display the surnames that match the search criteria. I got the db set up using phpmyadmin in about 2 minutes flat, but keep getting error messages when I write the php. I'm currently working with the below script, which keeps giving me the error 'unexpected T_string' on line 176 I've searched every forum and help site I can find, and the mysql and php manuals are just mind-boggling. I'm sure I'm making some really amateur mistake, but I'd really appreciate help with this! thanks all! <html> <head> <title>SEARCH RECORDS</title> </head> <body> <FORM NAME ="Search" METHOD ="POST" ACTION = "test3"> <INPUT TYPE = "TEXT" VALUE ="surname" NAME = "surname"> <INPUT TYPE = "Submit" Name = "Search" VALUE = "Search"> </FORM> </body> </html> <? $user_name = "*****"; $password = "*****"; $database = "*****"; $server = "localhost"; $db_handle = mysql_connect($server, $user_name, $password); $db_found = mysql_select_db($database, $db_handle); if ($db_found) { $SQL = "SELECT * FROM *****" WHERE surname=$_POST['surname']; $result = mysql_query($SQL); while ($db_field = mysql_fetch_assoc($result)) { print $db_field['Grave'] . "<BR>"; print $db_field['Surname'] . "<BR>"; print $db_field['Forenames'] . "<BR>"; print $db_field['Death_Date'] . "<BR>"; print $db_field['Birth_Year'] . "<BR>"; } mysql_close($db_handle); } else { print "Database NOT Found "; mysql_close($db_handle); } ?>