Page 1 of 2
Simple search MySQL
Posted: Sun Jan 08, 2006 8:15 pm
by Sculpture
Hello,
I have been trying for ages to get just a simple search to work
This is the table I want to search
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| entry_id | int(10) unsigned | | PRI | NULL | auto_increment |
| department_id | int(10) unsigned | | | 0 | |
| cat_id | int(10) unsigned | | | 0 | |
| name | varchar(40) | | | | |
| thumbnail | varchar(30) | | | | |
| description | text | YES | | NULL | |
| image | varchar(30) | | | | |
| advert | char(1) | YES | | NULL | |
+---------------+------------------+------+-----+---------+----------------+
I'd like to search the name and description fields.
This is what I have so far, or one of the many I have tried.
Code: Select all
<?php
$db = mysql_connect("localhost");
mysql_select_db("entry", $db);
$query = "select entry.name, entry.thumbnail from entry where name like '%".$name."%'";
$result = mysql_query($query);
while ($record = mysql_fetch_assoc($result){
while (list($fieldname, $fieldvalue) = each($record)){
echo $fieldname.": <b>".$fieldvalue."</B><br>";
}
echo "<br>";
}
?>
I would greatly appreciate any help,
thanks
Posted: Sun Jan 08, 2006 8:44 pm
by timvw
What isn't working? Have you echod the actual query to make sure it is what you think it is? Where does $name come from? How did you validate/sanitize it to be used in a query?
Posted: Sun Jan 08, 2006 10:06 pm
by Sculpture
Hello Timvw,
This is the form that I'm doing the search from.
Code: Select all
<form action="search_result.php" method="post" name="insert_form" id="insert_form">
<input name="name" type="text" id="name">
<input type="submit" value=" Search">
</form>
I am using a manual that is confusing the hell out of me and a CD tutorial.
Posted: Sun Jan 08, 2006 10:18 pm
by feyd
I'm going to guess that you assume $name will be automatically populated by the field "name" when it is not. Your code assumes register_globals is on when it is not. $_POST['name'] will be what you are looking for.
Posted: Sun Jan 08, 2006 10:36 pm
by Sculpture
Thank you Feyd,
I obviously do not know where to put the function you have suggested.
Code: Select all
<?php require_once('Connections/sports_connect.php'); ?>
<?php
mysql_select_db($database_sports_connect, $sports_connect);
$query_qSearch = "SELECT name, thumbnail FROM entry WHERE name LIKE $_POST['name']";
$qSearch = mysql_query($query_qSearch, $sports_connect) or die(mysql_error());
$row_qSearch = mysql_fetch_assoc($qSearch);
$totalRows_qSearch = mysql_num_rows($qSearch);
?>
Posted: Sun Jan 08, 2006 11:04 pm
by feyd
Posted: Mon Jan 09, 2006 3:01 am
by Sculpture
Ok,
This delivers very thing in the database. How do I reduce it to just what is asked?
Code: Select all
mysql_select_db($database_sports_connect, $sports_connect);
$query_qSearch = "SELECT name, thumbnail FROM entry ORDER BY name ASC";
$query_limit_qSearch = sprintf("%s LIMIT %d, %d", $query_qSearch, $startRow_qSearch, $maxRows_qSearch);
$qSearch = mysql_query($query_limit_qSearch, $sports_connect) or die(mysql_error());
$row_qSearch = mysql_fetch_assoc($qSearch);
Thanks guys
Posted: Mon Jan 09, 2006 3:13 am
by feyd
how does it deliver everything in the database? You're only requesting the first record found in that code.
Posted: Mon Jan 09, 2006 3:26 am
by Sculpture
Thanks for your help, it is greatly appreciated.
http://www.sportsmark.com.au
There are only 2 records in the database and they both contain the word "test" but if I type in something that should only bring up one record I get both.
Thanks again.
Posted: Mon Jan 09, 2006 3:45 am
by feyd
post the resultant query, your table structure, and the records so we can see if you have logic errors in your query, or if it lies somewhere else.
Posted: Mon Jan 09, 2006 4:52 pm
by Sculpture
Ok,
Code: Select all
<?php
if(!isset($tfm_rows) || intval($tfm_rows) == 0){$tfm_rows = 3;}
if(!isset($tfm_columns) || intval($tfm_columns) == 0){$tfm_columns = 3;}
if(!isset($tfm_vertical)){$tfm_vertical = "false";}
?>
<?php require_once('Connections/sports_connect.php'); ?>
<?php
$currentPage = $_SERVER["PHP_SELF"];
// Horizontal looper block 1 -- set up array, row and column values
$HLoop_qSearch = array();
$HLoop_qSearch_rows = $tfm_rows;
$HLoop_qSearch_columns = $tfm_columns;
$HLoop_qSearch_vertical = $tfm_vertical;
if($HLoop_qSearch_vertical == "true") {
$HLoop_qSearch_loopTo = $HLoop_qSearch_rows;
}else{
$HLoop_qSearch_loopTo = $HLoop_qSearch_columns;
}
$HLoop_qSearch_i = 0;
$HLoop_qSearch_ii = 0;
$HLoop_qSearch_actualrows = 1;
$maxRows_qSearch = $HLoop_qSearch_rows * $HLoop_qSearch_columns;
$pageNum_qSearch = 0;
if (isset($_GET['pageNum_qSearch'])) {
$pageNum_qSearch = $_GET['pageNum_qSearch'];
}
$startRow_qSearch = $pageNum_qSearch * $maxRows_qSearch;
mysql_select_db($database_sports_connect, $sports_connect);
$query_qSearch = "SELECT name, thumbnail FROM entry ORDER BY name ASC";
$query_limit_qSearch = sprintf("%s LIMIT %d, %d", $query_qSearch, $startRow_qSearch, $maxRows_qSearch);
$qSearch = mysql_query($query_limit_qSearch, $sports_connect) or die(mysql_error());
$row_qSearch = mysql_fetch_assoc($qSearch);
if (isset($_GET['totalRows_qSearch'])) {
$totalRows_qSearch = $_GET['totalRows_qSearch'];
} else {
$all_qSearch = mysql_query($query_qSearch);
$totalRows_qSearch = mysql_num_rows($all_qSearch);
}
$totalPages_qSearch = ceil($totalRows_qSearch/$maxRows_qSearch)-1;
if ($HLoop_qSearch_rows < 1){
$HLoop_qSearch_rows = ceil($totalRows_qSearch / $HLoop_qSearch_columns);
if($HLoop_qSearch_loopTo < 1) {
$HLoop_qSearch_loopTo = $HLoop_qSearch_rows;
}
}
$queryString_qSearch = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_qSearch") == false &&
stristr($param, "totalRows_qSearch") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_qSearch = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_qSearch = sprintf("&totalRows_qSearch=%d%s", $totalRows_qSearch, $queryString_qSearch);
?>
Code: Select all
<p><a href="/index.php" target="_top" onClick="MM_nbGroup('down','navbar1','home_page','assets/homepage/home_page_f3.jpg',1);" onMouseOver="MM_nbGroup('over','home_page','assets/homepage/home_page_f2.jpg','assets/homepage/home_page_f3.jpg',1);" onMouseOut="MM_nbGroup('out');"><img name="home_page" src="assets/homepage/home_page.jpg" width="140" height="30" border="0" alt=""></a> <br>
Records <?php echo ($startRow_qSearch + 1) ?> to <?php echo min($startRow_qSearch + $maxRows_qSearch, $totalRows_qSearch) ?> of <?php echo $totalRows_qSearch ?>
<table border="0" width="50%" align="center">
<tr>
<td width="23%" align="center"><?php if ($pageNum_qSearch > 0) { // Show if not first page ?>
<a href="<?php printf("%s?pageNum_qSearch=%d%s", $currentPage, 0, $queryString_qSearch); ?>">First</a>
<?php } // Show if not first page ?>
</td>
<td width="31%" align="center"><?php if ($pageNum_qSearch > 0) { // Show if not first page ?>
<a href="<?php printf("%s?pageNum_qSearch=%d%s", $currentPage, max(0, $pageNum_qSearch - 1), $queryString_qSearch); ?>">Previous</a>
<?php } // Show if not first page ?>
</td>
<td width="23%" align="center"><?php if ($pageNum_qSearch < $totalPages_qSearch) { // Show if not last page ?>
<a href="<?php printf("%s?pageNum_qSearch=%d%s", $currentPage, min($totalPages_qSearch, $pageNum_qSearch + 1), $queryString_qSearch); ?>">Next</a>
<?php } // Show if not last page ?>
</td>
<td width="23%" align="center"><?php if ($pageNum_qSearch < $totalPages_qSearch) { // Show if not last page ?>
<a href="<?php printf("%s?pageNum_qSearch=%d%s", $currentPage, $totalPages_qSearch, $queryString_qSearch); ?>">Last</a>
<?php } // Show if not last page ?>
</td>
</tr>
</table>
<br>
</div>
<div id="col1" align="left">
<?php
// Horizontal Looper start code for qSearch
do {
ob_start();
?>
<div id="result" align="left"><?php echo $row_qSearch['name']; ?>
<div align="center"><br>
<img src="images/<?php echo $row_qSearch['thumbnail']; ?>" width="150" height="160">
<p> </div>
</p>
</div>
<?php
// HLooper manage the arrays for qSearch
$HLoop_qSearch_temp = ob_get_contents(); //dump buffer to variable
ob_end_clean(); //clear buffer
$HLoop_qSearch[$HLoop_qSearch_i][$HLoop_qSearch_ii] = $HLoop_qSearch_temp;
$HLoop_qSearch_ii++;
if($HLoop_qSearch_ii >= $HLoop_qSearch_loopTo){
$HLoop_qSearch_i++;
$HLoop_qSearch_ii = 0;
$HLoop_qSearch_actualrows++;
};
} while ($row_qSearch = mysql_fetch_assoc($qSearch));
if($HLoop_qSearch_actualrows < $HLoop_qSearch_rows && $HLoop_qSearch_vertical == "false") $HLoop_qSearch_rows = $HLoop_qSearch_actualrows;
if($HLoop_qSearch_actualrows < $HLoop_qSearch_columns && $HLoop_qSearch_vertical == "true") $HLoop_qSearch_columns = $HLoop_qSearch_actualrows;
?>
<table>
<?php for($i = 0; $i < $HLoop_qSearch_rows; $i++) { ?>
<tr>
<?php for($ii = 0; $ii < $HLoop_qSearch_columns; $ii++) { ?>
<td><?php
if($HLoop_qSearch_vertical == "true") {
echo(isset($HLoop_qSearch[$ii][$i]) ? $HLoop_qSearch[$ii][$i] : " ");
}else{
echo(isset($HLoop_qSearch[$i][$ii]) ? $HLoop_qSearch[$i][$ii] : " ");
} ?></td>
<?php } ?>
</tr>
<?php } // End Horizontal/Vertical Looper code for qSearch ?>
</table>
Code: Select all
mysql> describe entry;
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| entry_id | int(10) unsigned | | PRI | NULL | auto_increment |
| department_id | int(10) unsigned | | | 0 | |
| cat_id | int(10) unsigned | | | 0 | |
| name | varchar(40) | | | | |
| thumbnail | varchar(30) | | | | |
| description | text | YES | | NULL | |
| image | varchar(30) | | | | |
| advert | char(1) | YES | | NULL | |
+---------------+------------------+------+-----+---------+----------------+
mysql> select * from entry;
+----------+---------------+--------+-------------+--------------+-----------------+--------------+--------+
| entry_id | department_id | cat_id | name | thumbnail | description | image | advert |
+----------+---------------+--------+-------------+--------------+-----------------+--------------+--------+
| 1 | 1 | 1 | Test AFL | afl_t.jpg | Testing AFL | afl_l.jpg | Y |
| 2 | 3 | 11 | Test Boxing | boxing_t.gif | Test for Boxing | boxing_l.gif | Y |
+----------+---------------+--------+-------------+--------------+-----------------+--------------+--------+
mysql> describe departments;
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
| department_id | tinyint(3) unsigned | | PRI | NULL | auto_increment |
| name | varchar(30) | | | | |
+---------------+---------------------+------+-----+---------+----------------+
mysql> describe category;
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
| cat_id | tinyint(3) unsigned | | PRI | NULL | auto_increment |
| department_id | tinyint(3) unsigned | | | 0 | |
| name | varchar(30) | | | | |
+---------------+---------------------+------+-----+---------+----------------+
Thank you indeed...!
Posted: Mon Jan 09, 2006 10:22 pm
by feyd
that appears to take care of all of my list except the most crucial: the resultant query. Since you are building a query string, I would like to see what is being sent to the database as the full query string. You can do this simply by echoing the query string just before calling the database query function.
Posted: Mon Jan 09, 2006 10:30 pm
by Sculpture
Is the select statment not the query string?
can you give me an example?
Code: Select all
<form action="search_result.php" method="post" name="search_f">
<div align="left">Search by name:<br>
<input name="name" type="text" size="15">
</div>
<p align="center">
<input type="submit" name="Submit" value="Search">
</form>
Thanks
Posted: Mon Jan 09, 2006 10:40 pm
by Sculpture
Do you mean something along the lines of:
Code: Select all
while ($record = mysql_fetch_assoc($result)){
while (list($fieldname, $fieldvalue) = each ($record){
echo $fieldname.": <B>".$fieldvalue."</B><BR>";
}
echo "<BR>";
}
........?
Posted: Mon Jan 09, 2006 10:48 pm
by feyd
you can edit you posts, fyi.
$query_limit_qSearch should be echo'd just before calling mysql_query(), that is what I said.