Forum Moderators: coopster
Im just learning how to query and present the data via web form...i have a page (code below) that i can not get to work, could anyone give me a pointer?
______________________________________________________________
<?php
$dbh=mysql_connect ("localhost", "username", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("database"); $dbh=mysql_connect ("localhost", "username", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("database");
if (isset($_POST['submit'])) {
if (!$_POST['lname'])
{
die('<table width="500" border="0" align="center" bgcolor="#C3C67C">
<tr>
<td><h1>You must enter a last name to query data</h1></td>
</tr>
</table>');
}
$lname = $_POST['lname'];
}
// Formulate Query
$query = sprintf("SELECT lname FROM Registration_Data WHERE lname='%s'",
mysql_real_escape_string($lname));
// Perform Query
$result = mysql_query($query);
// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "n";
$message .= 'Whole query: ' . $query;
die($message);
}
// Use result
mysql_fetch_row(), etc.
while ($row = mysql_fetch_assoc($result)) {
echo $row['lastname'];
}
mysql_free_result($result);
?>
<table width="100%" border="0" cellpadding="3" cellspacing="3" bgcolor="#C3C67C">
<form action="http://example.com/index.php?action=page_display&PageID=26" method="post">
<tr>
<td colspan="2"><div id="contactus_page" align="center"><h1>Lead Registration Form</h1><p><h3>*=Required Field</h3></p></div><hr color="#442E10">
</td>
</tr>
<tr>
<td>Last Name: </td>
<td><input name="lname" type="text" class="text" id="lname" size="20">*</td>
</tr>
<td> </td>
<td><input type="submit" name="submit" value="Register" class="button"></td>
</tr>
</form>
</table>
[edited by: eelixduppy at 8:08 pm (utc) on May 8, 2007]
[edit reason] please use example.com [/edit]
1. You call database twice (unnecessary, but not problematic)
2. I would make it easier:
if (!$lname = $_POST['lname'])
{
die('text to die...');
}
or if you feel uneasy:
if (!($lname = $_POST['lname']))...
3. Have you checked if $query is correct?
Try to show it, even when there is no mysql error and check if that query works as you wanted.
4. shorten performing query to:
$result = mysql_query($query) or die("Invalid query: " . mysql_error() . "<br>\nWhole query: $query");
5. And the main error:
// Use result
mysql_fetch_row(), etc.
while ($row = mysql_fetch_assoc($result)) {
first mysql_fetch_row fetches the first row, but you don't show it - get rid of it, and all should be fine then.
Hope this helps. If not, then please specify what the problem is.
Regards
Michal
I made a few of the adjustments you recommended, and im getting there....
I put the last name "mcgwire" in the text field, and it returned this:
mcgwiremcgwiremcgwire
which i verified, and i have 3 records with the last name of mcgiwre...
how can i return the entire record, and put one on each line?
for instance: i have fields in mysql dbase called: fname, address, city, state......etc.
$query = sprintf("SELECT * FROM Registration_Data WHERE lname='%s'",
mysql_real_escape_string($lname));
to retrieve everything
Then in while:
echo <table><tr><th...all headers</th></tr>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>{$row['lastname']}</td><td>{$row['address']}</td><td>{$row['city']}</td>...</tr>";
}
echo "</table>";
Hope this helps
Michal
while($a_var=mysql_fetch_array($result)) {
#Do some stuff like:
echo "<p>FName: ".$a_var['fname']."<br />Address: ".$a_var['address']."<br />City: ".$a_var['city']."<br /></p>";
# Then some other stuff.
# Should give you an idea or two.
} // Then end the loop.
Justin
* = all.
thanks for all the help....
ill let you know how things go and ask if i get stuck again...
here is what is returned:
First NameLast NameAddressCityState
mark mcgwire lkjlkjlk
killermcgwirenothingkillermcgwirenothing
the 2nd and 3 records do not show up in the proper table formatting?
check the page/html source and see if there is something wrong with how you are outputting tags.
check the delivered html first before playing with the loop, makes life easier.
could it just be a spacing issue?
<th...all headers</th>
tonynoriega,
You gotta be careful with us senior members...
Justin
Mine's got an extra <br />
And again, the first record comes back perfect....2nd and 3rd just look like pure data strings (example of output):
--------------------------------------------------------
First NameLast NameAddressCityState
mark mcgwire lkj lkj lk
//this below is the 2nd and 3rd records
killermcgwirenothingkillermcgwirenothing
--------------------------------------------------------
__________________________________________________________
$query = sprintf("SELECT * FROM Registration_Data WHERE lname='%s'",
mysql_real_escape_string($lname));
$result = mysql_query($query) or die("Invalid query: " . mysql_error() . "<br>nWhole query: $query");
echo "<table><tr><td>First Name</td><td>Last Name</td><td>Address</td><td>City</td><td>State</td></tr>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>{$row['fname']}</td><td>{$row['lname']}</td><td>{$row['address']}</td><td>{$row['city']}</td><td>{$row['state']}</td></tr>";
echo "</table>";
}
your closing table tag should be outside of your loop, like so
echo "<table><tr><td>First Name</td><td>Last Name</td><td>Address</td><td>City</td><td>State</td></tr>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>{$row['fname']}</td><td>{$row['lname']}</td><td>{$row['address']}</td><td>{$row['city']}</td><td>{$row['state']}</td></tr>";
}
echo "</table>";
worked perfect....
UDAMAN
When i query a last name that is not in the dbase, it just basically does nothing...returns nothing...can i do an if statement that says something similar to this below? I just cant seem to get this to work quite right....
if ($num_rows == 0) {
echo "Sorry, we have no records";
} else {
echo "<table><tr><td>First Name</td><td>Last Name</td><td>Address</td><td>City</td><td>State</td></tr>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>{$row['fname']}</td><td>{$row['lname']}</td><td>{$row['address']}</td><td>{$row['city']}</td><td>{$row['state']}</td></tr>";
}
echo "</table>";
}
though you would need to call mysql_num_rows first and I would use <= 0
$num_rows = mysql_num_rows($result);
if ($num_rows <= 0) {
....
to continue, again, if i may...the statement below. What if one of my fields to query was a list menu. For instance (im in real estate) i had an agnet searching leads who liked 3 bedrooms. My list menu has 2,3,4...etc. Would the "WHERE" statement be different than how the "lname='%s'"...does the %s only apply to text fields?
$query = sprintf("SELECT * FROM Registration_Data WHERE lname='%s'",
mysql_real_escape_string($lname));
The other option would be to use concatenation:
$query = "SELECT * FROM Registration_Data WHERE lname='" . mysql_real_escape_string($lname) . "'";
it's the same.
So just to answer your question, yes, the %s will suffice, but if you know, that it will be only integers, then you can choose %d - then all strings will be converted to "0";
Hope this helps you
Michal
PS. You'll get old enough :D
How to i retrieve an entire record, and allow my agents to modify it?
then re-submit to the database...
get one record:
$sql = "SELECT * FROM Registration_Data WHERE id='" . (int)$_GET['id'] . "'";
$query = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($query)) {
$row = mysql_fetch_assoc($query); //only one row, remember? - id is unique
echo "<table>";//show it in the table - it will look better this way
foreach($row as $key => $value) {
echo "<tr><td>$key</td><td><input type="text" name="$key" value="$value"></td></tr>";
}
echo "</table>";
}
But better would be to allow to change only specific rows, then you have to split it on your own. I mean use:
?>
<table>
<tr><td>Name</td><td><input type="text" name="name" value="<?php echo $row['name'];?>"></td></tr>
...etc for each row you want to show.
you insert into the db with command UPDATE [dev.mysql.com]
Hope this helps you
Michal
Here is a scenario i want to run by you to see if i am visualizing it correctly...
Say my users go to my "Query Records" page. They enter a last name of "Smith" it returns 5 records in that sweet table you showed me earlier....
ID - Fname - Lname - Address.....etc...etc.
23 Mister Smith 123 Road....etc...etc.
24 Jones Smith 456 Street....etc..etc...
They see that the address of "Jones Smith" should read 4567 Street.
Should i make a separate page that is strictly for modifying records? Say at the end of this table i have a link for each record that says "modify" and it takes them to the "Modify Record" page, where they enter the ID into the ID field, it pulls the data, then they UPDATE.... Would that be my best bet...
I work with Real Estate Agents...who are...lets say, particular about how things should function, whether its feasible or not...im trying to make this as easy as possible, which would be less admin overhead for me...
thanks
ID - Fname - Lname - Address...
23 Mister Smith 123 Road...<a href="modify.php?id=23">Modify</a><br />
24 Jones Smith 456 Street...<a href="modify.php?id=24">Modify</a>
But as I told you, the solution I gave you is more like an phpMyAdmin, where you have total control over everything.
For estate agents (read "normal" people) I would put only important and necessary information, read - I would not show ID:
$sql = "SELECT ID, fname, lname, address FROM ....";
?>
<table><tr>
<th>Name</th>
<th>Surname</th>
<th>Address</th>
</tr>
<?php
query and while($row = mysql_fetch_assoc(...)) {
echo "<tr><td>{$row['fname']}</td>
<td>{$row['lname']}</td>
<td>{$row['address']}</td>
<td><a href="modify.php?id={$row['ID']}"><img src="images/edit.png" border="0"></a></td>
</tr>";
}
echo "</table>";
and in modify.php:
<?php
$id = (int)$_GET['id']; // this is enough for security, really$sql = "SELECT ID, fname, lname, address FROM Registration_Data WHERE ID='$id'";
Hope this helps you
Michal
I do have access to a myPHPAdmin interface....
Now, how will the links dynamically show up?
what i mean is , will i have to go in and manually create the link for each record?
Im guessing, yes, because i dont know any other way to automate that process..?
<td>{$row['address']}</td>
<td><a href="modify.php?id={$row['ID']}">Modify</a></td>
</tr>";
I only changed image of editing to text "Modify", but I prefer a small icon of pencil on a written paper - it's smaller
Hope this helps :)
