form submission question

[Soulreaver418]

Trying to learn some php here to create a form for my company. Now, what I am trying to create is a page we can navigate to, enter a 4 digit code, and then have the corresponding information displayed from our mysql database.

We have multiple codes, starting at 1201 and each one corresponds to an address. I have populated my mysql tables with all of the codes and addresses, created a basic submit form and created a script to connect and pull data from the database.

I am lost when trying to figure out how to take the 4 digit code the user would input, pass it to the results.php file and displaying only the address from that properties code.

Here are my two php pages I made:

<form name="form1" method="POST" action="results.php"> 
<p> Code: <input name="code" type="text" id="code"> 
<p><input type="submit" name="submit" value="submit"> 
</form> 

and

<?php 
$host = "***"; 
$user = "***"; 
$pass = "***"; 
$dbname = "***";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>"); 
mysql_select_db($dbname); 

$data = mysql_query("SELECT * FROM keyconverter_mysql") 
or die(mysql_error()); 
Print "<table border cellpadding=3>"; 
while($info = mysql_fetch_array( $data ))
{
Print "<tr>"; 
Print "<th>Code:</th> <td>".$info['code'] . "</td></tr>";
Print "<th>Number:</th> <td>".$info['number'] . " </td></tr>";
Print "<th>Address:</th> <td>".$info['address'] . " </td></tr>";
Print "<th>Physical Address:</th> <td>".$info['physical address'] . " </td></tr>";
} 
Print "</table>"; 
?> 

Thanks for any help, totally lost here! =)

[Furiat]

Provided, that the code column in your database is of type INT, use this:
$code_modifier = '';
if (isset($_POST['code']))
{
$code_modifier = ' WHERE code = '.((int)$_POST['code']);
}
$data = mysql_query("SELECT * FROM keyconverter_mysql".$code_modifier)

If it's not of type INT, but of type VARCHAR or something to that extent, then the following would be apropriate:
$code_modifier = '';
if (isset($_POST['code']))
{
$code_modifier = ' WHERE code = \''.str_replace('\'','\\\'',$_POST['code']).'\'';
}
$data = mysql_query("SELECT * FROM keyconverter_mysql".$code_modifier)

And finally if the code passed by the user is to be treated like the "first X characters of the address code", then the code column would have to be of type VARCHAR and you would use the line:
$code_modifier = ' WHERE code LIKE \''.str_replace('\'','\\\'',$_POST['code']).'%\'';
in the corresponding spot.

EDIT: The below repsonse is better than mine ;)

[edited by: Furiat at 9:50 pm (utc) on Jun 7, 2010]

[Matthew1980]

Hi there Soulreaver418,

Welcome to the forum :)

Well the good news is your almost there! I can see from what you have posted that you have gone for the most part the correct way, the code that is entered by the user, would be passed to the php file that you referenced in the action part of the form tag - so, now you need to check for the submit being actioned and presence of a number being entered, something like this:-

<?php
check to see if the form has been posted
if(isset($_POST['submit']) && ($_POST['submit'] == "submit") && is_numeric($_POST['code'])){
$host = "***";
$user = "***";
$pass = "***";
$dbname = "***";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname, $connection);

$dbdata = mysql_query("SELECT * FROM `keyconverter_mysql` WHERE `your_code_field` = '".mysql_real_escape_string($_POST['code'])."'")or die(mysql_error());
//start html here
Print "<table border cellpadding=3>";
//loop through returned array
while($info = mysql_fetch_array( $dbdata ))//data is a reserved word changed to $dbdata
{
//process data here like you already were..
}
//finish the html this side of the loop
}else{
//redirect back to form if error occurs when processing
header("location: yourForm.php");
exit;
}
?>

The only other thing I notice was that you hadn't put the connection handle in the select_db() function, you would need it there, then subsequent mysql_ functions would inherit the last used or open connection from that point on

I have used mysql_real_escape_string() to protect your database against potential malicious code injection, I could have used strip_tags() too, but as there is a function to see if the value posted is_numeric() this negates the need for using strip_tags().

I would suggest in future that if you intend to generate html from php, try not to echo/print huge chunks of data, as this will put too much overhead on the parser/CPU - instead you can just break in and out of php, this also makes for easier html editing, and removes the need for toothpicking to escape double quotes. I hope I haven't confused you too much there ;)

And that's pretty much it, typed on-the-fly, so may need a bit of tweaking to suit your database column names :)

Hope that gets you in the right direction,

Cheers,
MRb

[Soulreaver418]

Ok, thanks a ton for the assistance. Getting some sort of an error when I hit submit on the first page. The free webhost I am using for now does not give an error message, but redirects me to a search page with err.ooowebhost.com in the address bar of ie so I have no way of seeing the error message..

Here is the code,

<?php
if(isset($_POST['submit']) && ($_POST['submit'] == "submit") && is_numeric($_POST['code'])){ 
$host = "mysql5.000webhost.com"; 
$user = "a6875524_pmrifl"; 
$pass = "v8mpir3z"; 
$dbname = "a6875524_pmrifl";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname, $connection);

$dbdata = mysql_query("SELECT * FROM `keyconverter_mysql` WHERE
`code` = '".mysql_real_escape_string($_POST['code'])."'")or
die(mysql_error()); 

Print "<table border cellpadding=3>"; 
while($info = mysql_fetch_array( $dbdata ))
{
Print "<tr>"; 
Print "<th>Code:</th> <td>".$info['code'] . "</td></tr>";
Print "<th>Number:</th> <td>".$info['number'] . " </td></tr>";
Print "<th>Address:</th> <td>".$info['address'] . " </td></tr>";
Print "<th>Physical Address:</th> <td>".$info['physical address'] . " </td></tr>";
Print "</table>";
}
exit;
} 
?> 

I took out the else statement thinking maybe I had screwed that up somehow but it still does not give results.

In php designer 7 it debugs ok, so I am not sure... here is the link to the submit section. you can enter any number at all or nothing at all and it still gives a generic error redirect.

[pmriflorida.comuf.com...]

[Matthew1980]

Hi there Soulreaver418,

If you are wanting to have the error messages echoed to screen, just put the error_reporting(E_ALL); at the top of the receiver script, this will then print any errors or notices that are flagged up, though it would be best to leave the else clause there as it is a means of redirecting the script to default if errors happen.

The script looks ok to me, Start with the basics first, make sure that you are getting the desired info from the form sent to the processing script, do a print_r($_POST); to see what is displayed (put it in the if clause as you need to check when submit is actioned).

You know that you can print_r($info) too, to see if the data is being fetch right:

echo "<pre>";
echo print_r($info);
echo "</pre>";

That will display the data - human readable inside the while if you place the code in the loop..

Not that it will make any difference, but use echo instead of print - or as I said before do this sort of thing:-

//get data
while(stuff){
?>
html code here<?php echo $aVar;?>more html here
<?php
}

Hope this helps,

Cheers,
MRb

[Soulreaver418]

OK, I tried what you suggested and it displayed one error. Fixed that error and now, when you fill out the digits in the code box and hit submit it loads a blank white page. (I temporarily took out the else again) Now, when I hit the blank white page, if I hit the browser back button and hit submit again it loads the data properly. Everything is formatted just like it should be.

I even tried entering some other codes and they all come up like they should but only after loading the blank page, then hitting browser back button and hitting submit again. >.<

[Matthew1980]

Hi there Soulreaver418,


Try this, I'll be stumped if this doesn't do it :|

This is the contents of results.php

<?php
//turn on error_reporting
error_reporting(E_ALL);

//catch incoming $_POST
if(isset($_POST['submit']) && ($_POST['submit'] == "submit") && is_numeric($_POST['code'])){

//Setup db info
$host = "HOST";
$user = "USER";
$pass = "PASSWORD";
$dbname = "DBNAME";

$connection = mysql_connect($host,$user,$pass) or die (mysql_error());
mysql_select_db($dbname, $connection);

$sqlQuery = "SELECT * FROM `keyconverter_mysql` WHERE `code` = '".mysql_real_escape_string($_POST['code'])."'";
$dbdata = mysql_query($sqlQuery) or die(mysql_error());
//Start table
?>
<table border cellpadding=3>
<?php
//start while loop
while($info = mysql_fetch_array($dbdata)){
//Create table structure
?>
<tr>
<th>Code:</th> <td><?php echo $info['code'];?></td></tr>
<th>Number:</th> <td><?php echo $info['number'];?></td></tr>
<th>Address:</th> <td><?php echo $info['address'];?></td></tr>
<th>Physical Address:</th> <td><?php echo $info['physical_address'];?></td></tr>
<?php
}
//close table after loop completed
?>
</table>
<?php
}
else{
//Error occured, redirect to formpage
header("location: theFormPage.php");
exit;
}
?>

Check your column names as $info['physical address']; isn't valid syntax I assume there is an underscore there ;) (Well I put one there anyway)

I have re done the code, this *should now work* Give it a try.

For reference, try to exemplify your details a bit, you don't want everyone to see your particulars when reading through posts ;)

Cheers,
MRb

[Soulreaver418]

Man, of all the simple little things to cause a problem. I ran that code that you put up and it gave me a new error message on line #31, the physical address. I looked it over and took out the _ in physical_ address and now no more error and it displays on first submit!

YAY!

I owe you a beer. =)

[Soulreaver418]

Ok, well the problem has arose again. I got one, a dropped connection to mysql database error. I think its the web host, free is not always good. Just wanted to use it to test with but I think I will just make a local mysql database and then upload to live once I am satisfied with it.

Thanks again for the assistance in filling in the blanks.

[Matthew1980]

Hi there Soulreaver418,

Lol, you made me wince then :)

I was hoping as that would do the trick, I'm glad it has. I will draw your attention to that column name in the DB though 'physical address', as I said, though on second thoughts it may well be good syntax, as its a heading - if you plan to use an UPDATE sql query any day soon, be sure to back tick the query, this will make the query more compatible to the space that's in the heading. If you can change the field name to physical_address, and change the php code to suite ;)

Sorry to babble on, but it better to do this now while you can IMO.

Glad your sorted anyway.

Have fun with the rest of the project.

Cheers,
MRb