Database search code created by DMX returns only 1st record

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Database search code created by DMX returns only 1st record

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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'] : '';
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

(!empty($_GET['member_name'])

you need another end )
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If you echo out $total_rows what does it tell you?

Mac
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

So is there more than one record with the same member_name?

Mac
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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>
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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%'";
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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.
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Thanks, lostboy.
That successfully solves issue #1.

(btw - love your tag line)
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Thank you, thank you, thank you! :D
Post Reply