Page 1 of 2

Database search code created by DMX returns only 1st record

Posted: Mon Apr 05, 2004 1:30 pm
by cdickson
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.

Posted: Mon Apr 05, 2004 2:59 pm
by twigletmac
If you replace your code with the following, does it work more as expected (be sure to check the comments)?

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'];
}
?>
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

Posted: Mon Apr 05, 2004 4:01 pm
by cdickson
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'] : '';

Posted: Mon Apr 05, 2004 6:59 pm
by tim
(!empty($_GET['member_name'])

you need another end )

Posted: Tue Apr 06, 2004 9:09 am
by cdickson
Thanks. I put the other end ) on so it reads:

Code: Select all

$search_name = (!empty($_GET['member_name'])) ? $_GET['member_name'] : '';
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

<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>&nbsp;</p>
                          </div></td>
                      </tr>
                    </table>
                  </form>

Posted: Tue Apr 06, 2004 10:47 am
by twigletmac
If you echo out $total_rows what does it tell you?

Mac

Posted: Tue Apr 06, 2004 11:03 am
by cdickson
If this is the correct syntax

Code: Select all

echo $total_rows = mysql_num_rows($result);
then I get the same result - just the first record in the database.

Posted: Tue Apr 06, 2004 12:01 pm
by twigletmac
So is there more than one record with the same member_name?

Mac

Posted: Tue Apr 06, 2004 12:27 pm
by cdickson
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:

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>&nbsp;</p>
                          </div></td>
                      </tr>
                    </table>
                  </form>

Posted: Tue Apr 06, 2004 12:43 pm
by lostboy
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%'";

Posted: Tue Apr 06, 2004 1:52 pm
by cdickson
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:

    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']; 
    } 
    ?>
    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
    $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;
    ?>
    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.
Thanks for your patience and assistance.

Posted: Tue Apr 06, 2004 2:17 pm
by lostboy

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

Posted: Tue Apr 06, 2004 2:47 pm
by cdickson
Thanks, lostboy.
That successfully solves issue #1.

(btw - love your tag line)

Posted: Tue Apr 06, 2004 3:02 pm
by lostboy
issue 2 - all records

$query_rsNameSearch = "SELECT * FROM Members";
no limit on search, * says select all

3) google pagination in php - lots of free scripts out there

Posted: Tue Apr 06, 2004 3:28 pm
by cdickson
Thank you, thank you, thank you! :D