Database search code created by DMX returns only 1st record
Moderator: General Moderators
Database search code created by DMX returns only 1st record
I have searched this forum for an answer without success or wouldn't dare post. Am a newbie - this is my first database search in DreamweaverMX.
Trying to: create search function for a chamber of commerce where visitors to the site can enter a business name or portion of the name and get a list of results.
The code that DMX created returns only the first record in the database, no matter what is entered into the field. The field to enter the info by which to search is named "searchName".
DMX Code is:
<?php
$searchName_rsNameSearch = "0";
if (isset($HTTP_GET_VARS['member_name'])) {
$searchName_rsNameSearch = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['member_name'] : addslashes($HTTP_GET_VARS['member_name']);
}
mysql_select_db($database_hschamber, $hschamber);
$query_rsNameSearch = sprintf("SELECT member_name, member_address1, member_address2, member_city, member_state, member_zip, member_phone, member_fax, member_email, contact_name, web_address FROM Members WHERE Members.member_name LIKE '$%s%%'", $searchName_rsNameSearch);
$rsNameSearch = mysql_query($query_rsNameSearch, $hschamber) or die(mysql_error());
$row_rsNameSearch = mysql_fetch_assoc($rsNameSearch);
$totalRows_rsNameSearch = mysql_num_rows($rsNameSearch);
?>
Help will be *genuinely* appreciated.
Trying to: create search function for a chamber of commerce where visitors to the site can enter a business name or portion of the name and get a list of results.
The code that DMX created returns only the first record in the database, no matter what is entered into the field. The field to enter the info by which to search is named "searchName".
DMX Code is:
<?php
$searchName_rsNameSearch = "0";
if (isset($HTTP_GET_VARS['member_name'])) {
$searchName_rsNameSearch = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['member_name'] : addslashes($HTTP_GET_VARS['member_name']);
}
mysql_select_db($database_hschamber, $hschamber);
$query_rsNameSearch = sprintf("SELECT member_name, member_address1, member_address2, member_city, member_state, member_zip, member_phone, member_fax, member_email, contact_name, web_address FROM Members WHERE Members.member_name LIKE '$%s%%'", $searchName_rsNameSearch);
$rsNameSearch = mysql_query($query_rsNameSearch, $hschamber) or die(mysql_error());
$row_rsNameSearch = mysql_fetch_assoc($rsNameSearch);
$totalRows_rsNameSearch = mysql_num_rows($rsNameSearch);
?>
Help will be *genuinely* appreciated.
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
If you replace your code with the following, does it work more as expected (be sure to check the comments)?
Personally I don't really rate DMX's PHP writing capabilities, it adds a fair bit of bloat in many instances and is not always straightfoward to debug.
Mac
Code: Select all
<?php
// first we want to make sure our users haven't got any spaces around
// the URL variable values so we trim them all.
foreach ($_GET as $key => $value) {
$_GET[$key] = trim($value);
}
// * lets use shorter variable names so it's not so much to type
// * we check to see whether the value of member_name in the URL is empty()
// or not
// * we use $_GET instead of $HTTP_GET_VARS because it's less to type,
// isn't deprecated and is only needed if you are using PHP older than
// version 4.1 (very unlikely).
// * since most servers have magic_quotes turned off we shouldn't have
// to deal with that either
$search_name = (!empty($_GET['member_name']) ? $_GET['member_name'] : '';
mysql_select_db($database_hschamber, $hschamber);
// using sprintf() for an SQL statement is entirely uneccessary in this
// case so we can change that (shorter variable names again too) we can
// also simplify some parts of the SQL statement
$sql = "SELECT member_name, member_address1, member_address2, member_city, member_state, member_zip, member_phone, member_fax, member_email, contact_name, web_address FROM Members WHERE member_name = '$search_name'";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
$total_rows = mysql_num_rows($result);
// now when you want to output the results you need to loop through the
// information that has been returned from the database (this is the
// step that the generated code was missing):
while ($row = mysql_fetch_assoc($result)) {
// now you can do your HTML display stuff, each variable can be accessed
// like so:
echo 'Member Name = '.$row['member_name'];
}
?>Mac
I may be new to this, but I was wondering about the unfamiliar code that I saw DMX generate. Think I'll do some more homework and stick to hand coding.
I copied and pasted your code - and thank you for the detailed comments as to WHY things are done this way.
But now I am getting a parsing error on the line that contains the code:
$search_name = (!empty($_GET['member_name']) ? $_GET['member_name'] : '';
I copied and pasted your code - and thank you for the detailed comments as to WHY things are done this way.
But now I am getting a parsing error on the line that contains the code:
$search_name = (!empty($_GET['member_name']) ? $_GET['member_name'] : '';
Thanks. I put the other end ) on so it reads:
but I'm still getting only the first record of the database, no matter what I type into the search field.
I have the following form set up to perform the search. Perhaps I have done something horribly wrong here that won't return proper search results:
Code: Select all
$search_name = (!empty($_GET['member_name'])) ? $_GET['member_name'] : '';I have the following form set up to perform the search. Perhaps I have done something horribly wrong here that won't return proper search results:
Code: Select all
<form name="nameSearch" method="post" action="alpha.php">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>
<td class="bodytext" scope="col"><div align="left">Search by Business Name</div></td>
</tr>
<tr>
<td class="bodytext" scope="col"><p>
<input name="search_name" type="text" id="search_name">
</p> </td>
</tr>
<tr>
<td class="bodytext" scope="col"><input type="submit" name="Submit" value="Search"></td>
</tr>
<tr valign="middle">
<td height="30" class="bodytext"><div align="center">
<p> </p>
</div></td>
</tr>
</table>
</form>- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
If this is the correct syntax
then I get the same result - just the first record in the database.
Code: Select all
echo $total_rows = mysql_num_rows($result);- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
No the member_name fields are all different. There are approximately 500 total members in the database, all with unique names.
My intent is to have someone be able to enter a partial business name into the search_name field and bring up a list of "LIKE" names.
I understand that the way it is currently coded it will only work if the exact name is typed in, but I have tried it that way without success as well.
Here is the form code that is supposed to generate the search:
My intent is to have someone be able to enter a partial business name into the search_name field and bring up a list of "LIKE" names.
I understand that the way it is currently coded it will only work if the exact name is typed in, but I have tried it that way without success as well.
Here is the form code that is supposed to generate the search:
Code: Select all
<form name="nameSearch" method="post" action="alpha.php">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>
<td class="bodytext" scope="col"><div align="left">Search by Business Name</div></td>
</tr>
<tr>
<td class="bodytext" scope="col"><p>
<input name="search_name" type="text" id="search_name">
</p> </td>
</tr>
<tr>
<td class="bodytext" scope="col"><input type="submit" name="Submit" value="Search"></td>
</tr>
<tr valign="middle">
<td height="30" class="bodytext"><div align="center">
<p> </p>
</div></td>
</tr>
</table>
</form>if a partial match, where do you want to match from beginning of word, end of word, middle? I'll assume middle and add the wildcard character % before and after the $search_name and add the LIKE keyword
Code: Select all
$sql = "SELECT member_name, member_address1, member_address2, member_city, member_state, member_zip, member_phone, member_fax, member_email, contact_name, web_address FROM Members WHERE member_name LIKE '%$search_name%'";I'm getting closer, but now I have a couple of resulting issues:
- 1 - When the search page (searchName.php) comes up, without ever hitting the Submit button or entering any search criteria, ALL member names appear at the top of this page. It is a continuous run of unformatted text, black on white. Then the header images begin and the rest of the page appears with the search form on it.
Query code is:2 - Now search results are come through, but ALL member names always appear, regardless of what is typed into the search field. The query code on the results page is:Code: Select all
<?php foreach ($_GET as $key => $value) { $_GET[$key] = trim($value); } $search_name = (!empty($_GET['member_name'])) ? $_GET['member_name'] : ''; mysql_select_db($database_hschamber, $hschamber); $sql = "SELECT member_name, member_address1, member_address2, member_city, member_state, member_zip, member_phone, member_fax, member_email, contact_name, web_address FROM Members WHERE member_name LIKE '%$search_name%'"; $result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>'); $total_rows = mysql_num_rows($result); while ($row = mysql_fetch_assoc($result)) { echo 'Member Name = '.$row['member_name']; } ?>3 - Need to know how to create multiple pages in the event more than the max number of 20 results show. Know that arrays might work but don't know if I know enough to attack this yet.Code: Select all
<?php $maxRows_rsNameSearch = 20; $pageNum_rsNameSearch = 0; if (isset($_GET['pageNum_rsNameSearch'])) { $pageNum_rsNameSearch = $_GET['pageNum_rsNameSearch']; } $startRow_rsNameSearch = $pageNum_rsNameSearch * $maxRows_rsNameSearch; mysql_select_db($database_hschamber, $hschamber); $query_rsNameSearch = "SELECT * FROM Members"; $query_limit_rsNameSearch = sprintf("%s LIMIT %d, %d", $query_rsNameSearch, $startRow_rsNameSearch, $maxRows_rsNameSearch); $rsNameSearch = mysql_query($query_limit_rsNameSearch, $hschamber) or die(mysql_error()); $row_rsNameSearch = mysql_fetch_assoc($rsNameSearch); if (isset($_GET['totalRows_rsNameSearch'])) { $totalRows_rsNameSearch = $_GET['totalRows_rsNameSearch']; } else { $all_rsNameSearch = mysql_query($query_rsNameSearch); $totalRows_rsNameSearch = mysql_num_rows($all_rsNameSearch); } $totalPages_rsNameSearch = ceil($totalRows_rsNameSearch/$maxRows_rsNameSearch)-1; ?>
Code: Select all
while ($row = mysql_fetch_assoc($result)) {
echo 'Member Name = '.$row['member_name'];
}is the code that prints out all the records to the screen