Page 1 of 1
query db if name entered exists
Posted: Sun Oct 30, 2005 3:48 pm
by Kilgore Trout
summary - old to scripting, new to php/mysql. i have read too many manuals. working on my first database job, it’s a site displaying commercial real estate properties. my client wants to enter data about the properties into the database via web form. that i figured out. now what i’m trying to do is to allow him to pull up a record in that same form by entering in the name of the property, in order to alter it and resubmit it. all i need here is some direction i think. i’m guessing that i need some way
of checking to see if the name typed in exists. IF it does not exist, then INSERT new record. ELSE if it already exists, and only the name is filled in, then pull up the record fields and display them in the form, probably using the value=$variable fields in the form. am i on the right track? is there a better way?
if i am on the right track or if perhaps the previous paragraph just doesn’t make any sense, how would i query the database to find out if the name of a record entered already exists in the db? i’m working on a simple test script with only three fields, my most recent attempt is below but never matches up with the name already in the db.
Code: Select all
$name = $_POST["name"];
$len = strlen($name);
if ($len > 0)
{
$query = "SELECT name FROM picsTBL";
$response = "mysql_query($query, $connection) or die(mysql_error())";
if ($name != $response){
echo "NEW RECORD!";
}else{
echo "ALREADY IN DATABASE!";
}
$jpg = $_POST["jpg"];
$query = "INSERT INTO pics (autoID, name, jpg) VALUES (NULL, '$name', '$jpg')";
mysql_query($query, $connection) or die(mysql_error());
}
any thoghts are appreciated
Posted: Sun Oct 30, 2005 4:00 pm
by Chris Corbyn
The way you describe your logic sounds good. Check for records that seem to fit, if there are any (or multiple), show the actual names, then click one to proceed to the form, with the values pre-filled. If no records fit, then start a new blank form.
This is basic, but it's a start.
Code: Select all
$name = $_POST["name"];
$len = strlen($name);
if ($len > 0)
{
$query = "
select
name
from
theTBL
where
name like '%$name%'";
$result = mysql_query($query, $connection) or die(mysql_error());
if (mysql_num_rows($result) > 0)
{
echo "The following records seem to fit, plase choose one from below:\n<br />";
while ($row = mysql_fetch_array($result))
{
echo $row['name']."<br />\n";
}
}
else
{
echo "No results found...<br />\n"; //And then off to a blank form
}
}
Posted: Sun Oct 30, 2005 4:24 pm
by dallasx
Code: Select all
$name = $_POST["name"]; // take out the double quotes
$name = $_POST['name']; // use single quotes
Posted: Sun Oct 30, 2005 4:43 pm
by Chris Corbyn
dallasx wrote:Code: Select all
$name = $_POST["name"]; // take out the double quotes
$name = $_POST['name']; // use single quotes
Why? Other than a small speed increase...
Posted: Sun Oct 30, 2005 5:18 pm
by dallasx
Small speed increase

Looks a little cleaner, just a preference that I thought I'd pass along.
Posted: Sun Oct 30, 2005 6:17 pm
by yum-jelly
Why does one copy variables, I have never understood that! I mean is it worth pushing more on to the stack (memory) just because you don't want to write $_POST['var']. If your script becomes really big and your service really busy, it can slow your server down greatly!
yj
Posted: Sun Oct 30, 2005 6:20 pm
by Chris Corbyn
yum-jelly wrote:Why does one copy variables, I have never understood that! I mean is it worth pushing more on to the stack (memory) just because you don't want to write $_POST['var']. If your script becomes really big and your service really busy, it can slow your server down greatly!
yj
Because it takes far less time to modify a PHP script that uses the POST vars if some web desinger changes the name of the form attribute somewhere along the line

The amount of memory we're talking about is, well, not worth talking about.
nodding off
Posted: Mon Oct 31, 2005 12:45 am
by Kilgore Trout
thanks for the help! my test script now checks to see if the record name exists, and if it does not it writes a new record, uploads a file and records the file name in the database.
NOW, it’s 1:30am and i am stumped. when the record name does exist already, i want to pull up the rest of the record in the same form that was just submitted. i got as far as echoing all fields in plain html, so they ARE there. but i can’t seem to get the the fields to fill itself in. my brain is mush, i’m going to bed now, but if anyone has any ideas, i’ll be grateful in my sleep. here’s the whole test code with the html form.
Code: Select all
<?php
require ($_SERVER["DOCUMENT_ROOT"]."/config/db_config_sucks.php");
$connection = @mysql_connect($db_host, $db_user, $db_password) or die("error connecting");
mysql_select_db($db_name, $connection);
$name = $_POST['name'];
$len = strlen($name);
if ($len > 0)
{
// make and store query to test
$query = "SELECT * FROM pics WHERE name like '$name'";
$result = mysql_query($query, $connection);
$row = mysql_fetch_assoc($result);
// test to see if it is new or old record name
if (mysql_num_rows($result) > 0){ //exists, so display record to update
echo "ALREADY IN DATABASE!";
echo "<br>autoID: ".$row["autoID"];
echo "<br>JPG Name: ".$row["name"];
echo "<br>Photo: ".$row["jpg"];
echo "<br>Comment: ".$row["comment"];
}else{ //insert this new record into db
// $jpg = $_POST["jpg"]; - unnecessary post
$comment = $_POST["comment"];
echo "NEW RECORD!";
$query = "INSERT INTO pics (autoID, name, jpg, comment) VALUES (NULL, '$name', '".$_FILES["jpg"]["name"]."', '$comment')";
mysql_query($query, $connection) or die(mysql_error());
//upload photo file
$filename = trim($_FILES['jpg']['name']);
$filename = substr($filename, -20);
$filename = ereg_replace(" ", "", $filename);
if((ereg(".jpg", $filename)) || (ereg(".gif", $filename)) || (ereg(".JPG", $filename)) || (ereg(".GIF", $filename)))
{
$uploaddir = "photos/";
$uploadfile = $uploaddir . $filename;
move_uploaded_file($_FILES['jpg']['tmp_name'], $uploadfile);
}else{
echo "<br>Only images are allowed, upload failed";
}
}
}
$serve = "$_SERVER[PHP_SELF]";
$form = <<<EOFORM
<html>
<head>
<title>SUCKS</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="main.css" rel="stylesheet" type="text/css">
</head><body><br><br><br><br><br><br><br><br>
<body><center>
<form action="$serve" method="POST" enctype="multipart/form-data" name="form2">
<table width="752" border="3" align="center" cellpadding="0" cellspacing="0" bordercolor="#000000">
<font color="#FFFFFF" face="arial" size="2">
<tr class="topbottomborder">
<td height="34" valign="middle" bgcolor="#CCCCCC" class="topbottomborder">
<div align="right">JPG Name:</div></td>
<td colspan="3" valign="middle" bgcolor="#CCCCCC" class="topbottomborder">
<input name="name" type="text" size="90" value="$name"</td>
</tr>
<tr class="topbottomborder">
<td height="32" valign="middle" bgcolor="#FFFFFF" class="topbottomborder">
<div align="right">Photo:</div></td>
<td colspan="3" valign="middle" bgcolor="#FFFFFF" class="topbottomborder">
<input type="hidden" name="MAX_FILE_SIZE" value="2097152">
<input name="jpg" type="file" size="40" value="$jpg"></td>
</tr>
<tr class="topbottomborder">
<td height="34" valign="middle" bgcolor="#CCCCCC" class="topbottomborder">
<div align="right">JPG Comment:</div></td>
<td colspan="3" valign="middle" bgcolor="#CCCCCC" class="topbottomborder">
<input name="comment" type="text" size="90" value="$comment"</td>
</tr>
<tr class="topbottomborder">
<td height="34" colspan="4" valign="middle" bgcolor="#FFFFFF" >
<div align="right"><input type="submit" name="Save" value="Save Entries"></div>
</td>
</tr>
</font>
</table>
</form>
</body>
</html>
EOFORM;
echo $form;
?>