Help interacting with a simple MySQL DB via PHP

crazysaguaro

Hello - I've created an extremely simple table in MySQL and displayed it using PHP at viewall_records.php.

The PHP code is:

<?php  

// open the connection 
$conn = mysql_connect ("localhost", "solidliq_ii", "");  

// pick the database to use 
mysql_select_db ("solidliq_ii",$conn);  

// create the sql command 
$sql = "Select * from shutter_inventory";  

// execute the sql command 
$result = mysql_query($sql, $conn) or die (mysql_error());  

echo "<table border=1><tr> 
          <th>Customer ID</th> 
          <th>Last Name</th> 
          <th>Address</th> 
          <th>City</th>
          <th>State</th>
          <th>Zip Code</th>
	  <th>Delivery Date</th></tr>"; 


// go through each row and display data 
while ($whatever = mysql_fetch_array($result)) {  

// give a name to the fields 
$id = $whatever['customer_id'];  

$name = $whatever['customer_name'];  

$address = $whatever['customer_address'];  

$city = $whatever ['customer_city'];  

$state = $whatever ['customer_state'];  

$zipcode = $whatever ['customer_zipcode'];  

$date = $whatever ['customer_date'];  

//print the results on screen 
echo "<tr><td>" .$id. "</td>"; 
echo "<td>" .$name. "</td>"; 
echo "<td>" .$address. "</td>"; 
echo "<td>" .$city. "</td>"; 
echo "<td>" .$state. "</td>"; 
echo "<td>" .$zipcode. "</td>"; 
echo "<td>" .$date. "</td></tr>";
}  

echo "</table>"; 
?>

I want to be able to create two more simple PHP files to interact with the MySQL database I built; add_records.php and search_records.php.

add_records.php would be an area to add data to the MySQL table via fields in a web browser.

search_records.php would be an area to search by customer name or customer ID number and display matching results. For example, if the last name typed in was smith, the code would return any rows matching 'smith' in the browser.

Are these things easy and possible? I'm a PHP/MySQL newbie, and have learned a lot in the past few days - just not enough to start on the other two files. If anyone could get me started or point me to posts/documentation/URLs that would help me, I would greatly appreciate it.

Thanks in advance for your help!

-Gina

mscreashuns

I'm not sure about the search one, but for add_records.php, you could set up a form like this. ("___"=textbox)

INPUT1:
INPUT2:
INPUT3:___
...

assuming you know how to set up a form. (if not I can help you)

Then, for the php part, put this:

<?php

// Make a MySQL Connection
mysql_connect("HOST_NAME", "USER_NAME", "PASSWORD") or die(mysql_error());
mysql_select_db("DATABASE_NAME") or die(mysql_error());

// Insert a row of information into the table "TABLE_NAME"
mysql_query("INSERT INTO TABLE_NAME 
(c1, c2 ) VALUES('$input1', '$input2' ) ") 
or die(mysql_error());
?>

If you don't understand yet, make your form, and then come pm me and I can help you set up the rest. 😃

edcellgavin

i've found this simple yet very interesting post... download the zip file
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=600&lngWId=8

also, for the seach_records.php,

add, fields to query in the form, eg
<form action="seach_records.php" method="post">
<input type="textbox" name="id" value="">
<input type="textbox" name="name" value="">
<input type="submit" value="search">
</form>

and your php query would be something like this
<?php

// simple function to add query expressions
function mySQLAddClause($strOldString, $strNewString, $strConnector) {
	if (strlen(trim($strOldString)) != 0 && strlen(trim($strNewString)) != 0) {
		return "$strOldString $strConnector $strNewString";
	}
	elseif (strlen(trim($strOldString)) == 0 && strlen(trim($strNewString)) != 0) {
		return $strNewString;
	}
	elseif (strlen(trim($strOldString)) != 0 && strlen(trim($strNewString) == 0)) {
		return $strOldString;
	}
	else return "";
}

$id = addslashes($_POST['id']);
$name = addslashes($_POST['name']);

$filter = "";
if (strlen(trim($id)) > 0)   $filter = mySQLAddClause($filter, "customer_id = '$id'", "AND");
if (strlen(trim($name)) > 0) $filter = mySQLAddClause($filter, "customer_name LIKE '%$name%'", "AND");

$db = mysql_connect("host", "username", password);
mysql_select_db("database_name");

$query  = "SELECT * FROM shutter_inventory";
if (strlen(trim($filter)) > 0) $query.= " WHERE ($filter)";
$query .= " ORDER BY Name";
$query .= " LIMIT 20";
$result = mysql_query($query, $db);
while ($row = mysql_fetch_object($result)) {
	// display records
}

crazysaguaro

Thanks for the reply!

For the search records part, the HTML is here.

And the PHP code looks like this:

<?php 

// simple function to add query expressions 
function mySQLAddClause($strOldString, $strNewString, $strConnector) { 
if (strlen(trim($strOldString)) != 0 && strlen(trim($strNewString)) != 0) { 
return "$strOldString $strConnector $strNewString"; 
} 
elseif (strlen(trim($strOldString)) == 0 && strlen(trim($strNewString)) != 0) { 
return $strNewString; 
} 
elseif (strlen(trim($strOldString)) != 0 && strlen(trim($strNewString) == 0)) { 
return $strOldString; 
} 
else return ""; 
} 

$id = addslashes($_POST['id']); 
$name = addslashes($_POST['name']); 

$filter = ""; 
if (strlen(trim($id)) > 0) $filter = mySQLAddClause($filter, "customer_id = '$id'", "AND"); 
if (strlen(trim($name)) > 0) $filter = mySQLAddClause($filter, "customer_name LIKE '%$name%'", "AND"); 

$db = mysql_connect("localhost", "solidliq_ii", ""); 
mysql_select_db("solidliq_ii"); 

$query = "SELECT * FROM shutter_inventory"; 
if (strlen(trim($filter)) > 0) $query.= " WHERE ($filter)"; 
$query .= " ORDER BY Name"; 
$query .= " LIMIT 20"; 
$result = mysql_query($query, $db); 
while ($row = mysql_fetch_object($result)) { 
// display records 
} 

?>

And I'm getting the error:
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/solidliq/public_html/dev/ii/search_records.php on line 32

Thoughts?

-Gina

crazysaguaro

The add_records.php script and method above works great.

The only thing is, that it brings you back to a blank screen after adding a record. Any way to bring it to the original page (add_record.htm) or a page that says something like 'Record Successful! Add Another?') - success.htm?

add_record.htm page: http://www.solidliquiddesigns.com/dev/ii/add_records.htm

add_record.php used:

<?php 

// Make a MySQL Connection 
mysql_connect("localhost", "solidliq_ii", "") or die(mysql_error()); 
mysql_select_db("solidliq_ii") or die(mysql_error()); 

// Insert a row of information into the table "shutter_inventory" 
mysql_query("INSERT INTO shutter_inventory  

(customer_id, customer_name, customer_address, customer_city, customer_state, customer_zipcode, 

customer_date ) VALUES('$id', '$name', '$address', '$city', '$state', '$zipcode', '$date' ) ")  

or die(mysql_error()); 
?>

You can view records added at:
http://www.solidliquiddesigns.com/dev/ii/viewall_records.php

Or see directory listing at:
http://www.solidliquiddesigns.com/dev/ii

Thank you!

-Gina

edcellgavin

could u pls. print the query string?
type this : print "$query";
before this line : $result = mysql_query($query, $db);

so you'll know if the query is valid.

regarding ur other post
The add_records.php script and method above works great...

you can use this line to confirm that the insert operation was successful:

if (mysql_num_rows($query) > 0) {
header("Location:success.htm"); // redirect to another page
// or just print to screen
$msg = "record has been saved.";
}
else {
$msg = "record not saved.";
}

print "$msg<a href=\"add_records.php\">Add a new record</a>";

crazysaguaro

I added the print query string, and now get the error (when searching for name "jones"):
SELECT * FROM shutter_inventory WHERE (customer_name LIKE '%jones%') ORDER BY Name LIMIT 20
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/solidliq/public_html/dev/ii/search_records.php on line 33

http://www.solidliquiddesigns.com/dev/ii/search_records.htm

edcellgavin

change
SELECT * FROM shutter_inventory WHERE (customer_name LIKE '%jones%') ORDER BY Name LIMIT 20

to
SELECT * FROM shutter_inventory WHERE (customer_name LIKE '%jones%') ORDER BY customer_name LIMIT 20

i believe there's no 'Name' field in the table...

crazysaguaro

Okay, I changed the name area to customer_name and also removed the percentage symbols from around '%$name%' and it seems to work, but is not displaying results: http://www.solidliquiddesigns.com/dev/ii/search_records.htm

I'd like it to display results like my other php page at http://www.solidliquiddesigns.com/dev/ii/viewall_records.php

-Gina

edcellgavin

don't remove the '%' coz its a wildcard character...

for instance, you want to search for records whose surname has 'saguaro' in it. the where expression - "where customer_name like '%saguaro%'" will look for any record that has the term 'saguaro' in front, middle or last part of the customer_name field. removing the '%' will search for exact matches only.

crazysaguaro

Thanks for the tip ... I removed those because I didn't know what they did and I thought it was creating an error! I've put them back now. 🙂

I just need to get the search results displaying; I search for things and nothing comes up but the query (because we placed the print "$query" line) ... but if I remove that line, I'm left with a blank white screen for the results.

Suggestions? The code for search_records.php is:

<?php 

// simple function to add query expressions 
function mySQLAddClause($strOldString, $strNewString, $strConnector) { 
if (strlen(trim($strOldString)) != 0 && strlen(trim($strNewString)) != 0) { 
return "$strOldString $strConnector $strNewString"; 
} 
elseif (strlen(trim($strOldString)) == 0 && strlen(trim($strNewString)) != 0) { 
return $strNewString; 
} 
elseif (strlen(trim($strOldString)) != 0 && strlen(trim($strNewString) == 0)) { 
return $strOldString; 
} 
else return ""; 
} 

$id = addslashes($_POST['id']); 
$name = addslashes($_POST['name']); 

$filter = ""; 
if (strlen(trim($id)) > 0) $filter = mySQLAddClause($filter, "customer_id = '$id'", "AND"); 
if (strlen(trim($name)) > 0) $filter = mySQLAddClause($filter, "customer_name LIKE '%$name%'", "AND"); 
if (strlen(trim($order)) > 0) $filter = mySQLAddClause($filter, "order_no = '$order'", "AND"); 

$db = mysql_connect("localhost", "solidliq_ii", ""); 
mysql_select_db("solidliq_ii"); 

$query = "SELECT * FROM shutter_inventory"; 
if (strlen(trim($filter)) > 0) $query.= " WHERE ($filter)"; 
$query .= " ORDER BY customer_name"; 
$query .= " LIMIT 20"; 
print "$query"; 
$result = mysql_query($query, $db); 
while ($row = mysql_fetch_object($result)) { 
// display records 
} 

?>

edcellgavin

in your loop statement,

while ($row = mysql_fetch_object($result)) { 
// display records 
}

try adding some code in between to display the result.
e.g.

while ($row = mysql_fetch_object($result)) { 
// display records where field1 and field2 are the fields in shutter_inventory table and are included in the select query.

print $row->field1 "\n";
print $row->field2 "\n";
print "\n";
}

crazysaguaro

Well, I added all of the fields in my table to the page:

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

// display records
print $row->order_no "\n"; 
print $row->customer_id "\n"; 
print $row->customer_name "\n"; 
print $row->customer_address "\n"; 
print $row->customer_city "\n"; 
print $row->customer_state "\n"; 
print $row->customer_zipcode "\n"; 
print $row->customer_date "\n"; 
print "\n"; 
}

And still no result:
http://www.solidliquiddesigns.com/dev/ii/search_records.htm

If you or anyone else has any more ideas to try to get this going, I'd appreciate it! 🙂

-Gina

edcellgavin

try adding this line before your while statement

print mysql_num_rows($result);

to check the number of records matching your query.

crazysaguaro

I added; and received the error:

[FONT=courier new]Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/solidliq/public_html/dev/ii/search_records.php on line 39[/FONT]

Line 39 is:

print $row->order_no "\n";

The entire code attached.

-Gina

edcellgavin

try changing this line

print $row->order_no "\n";

 
print $row->order_no . "\n";

notice the '.' which concatenates the object variable $row->order_no and '\n'.

add

print mysql_num_rows($result);

to return the number of records returned by the query

crazysaguaro

It works!! 😃

I formatted the results to display in a table, it looks great. Thanks so much for your help!

http://www.solidliquiddesigns.com/dev/ii/search_records.htm