Loop through to get all records that match search criteria

[wakk0]

I have a form where the user can search by 2 of 3 criteria. i would like for it to output all the matching results into another form. currently it is only outputting the first record.

switch ($ACTION) {
	case 'search':
		$DATA = $_REQUEST['data'];

		if($DATA['phone']) $WHERE_CLAUSE[] = "phone='".$db->escape($DATA['phone'])."'"; else unset($DATA['phone']);
		if($DATA['last']) $WHERE_CLAUSE[] = "last='".$db->escape($DATA['last'])."'"; else unset($DATA['last']);
		if($DATA['email']) $WHERE_CLAUSE[] = "email='".$db->escape($DATA['email'])."'"; else unset($DATA['email']);

		if(count($DATA)>=2){
			$WHERE_CLAUSE = implode(' AND ', $WHERE_CLAUSE);
			switch ($ACTION) {
				case 'search':
		
					$sql = "
						SELECT * FROM card WHERE register='1' AND $WHERE_CLAUSE
					";
					$CARD = mysql_fetch_array($sql); 

					
					if(!$CARD) $message = 'There is no card number associated with the information you provided';
				break;
			}
		} else {
			$message = 'Minimum two fields are required';
		}
	break;


if(!$message)
$transfer_box = '
<form name="form2" id="form2" method="post" action="value_transfertest.php" onSubmit="return ValidateForm()">
<input type="hidden" name="action" value="transfer" />
<p>
	<font size="3" face="Verdana, Arial, Helvetica, sans-serif">TRANSFER</font>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter OLD Value Card number here:
	<br>
	
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/></font>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter NEW Value Card number here:
		<input type="text" name="newpc" maxlength="8"/>
	</font>
</p>
<p>
	<font face="Verdana, Arial, Helvetica, sans-serif"> <br />
	<input type="submit" name="Submit2" value="Transfer the Values" onclick="return confirm(\\'Are you sure you want to transfer the Punches and Points from \\'+document.form2.oldpc.value+\\' to \\'+document.form2.newpc.value+\\'?\\')"/>
	</font>
</p>
</form>
';
else
$transfer_box = $message;
 
$search_box = '
<form name="form1" id="form1" method="post" action="value_transfertest.php">
<input type="hidden" name="action" value="search">
<p>
	<font size="1" face="Verdana, Arial, Helvetica, sans-serif">Optional: </font>
	<font size="3" face="Verdana, Arial, Helvetica, sans-serif">LOOK UP A CARD NUMBER <font size="2">(Enter 2 of the following):</font></font>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Phone Number: </font>
	<input type="text" name="data[phone]" value="'.stripslashes($DATA[phone]).'"/>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Last Name: </font>
	<input type="text" name="data[last]" value="'.stripslashes($DATA[last]).'"/>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Email Address: </font>
	<input type="text" name="data[email]" value="'.stripslashes($DATA[email]).'"/>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">
	<input type="submit" name="Submit" value="Look up card" />
	</font>
	
</p>
</form>
';

[adrian_schmidt]

There’s no loop in your code! Or did I miss it?

It doesn’t look like you actually perform the SQL query to begin with. But even if you do, you will need to loop through the result fetching all rows as arrays. You’re only fetching one (the first) right now.

[wakk0]

i’m trying to add the code that you suggested into my code, but when i do it really screws up my form.

if(!$message) 
$transfer_box = '
<form name="form2" id="form2" method="post" action="value_transfertest.php" onSubmit="return ValidateForm()">
<input type="hidden" name="action" value="transfer" />
<p>
	<font size="3" face="Verdana, Arial, Helvetica, sans-serif">TRANSFER</font>
</p>
<p>
 
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter OLD Value Card number here:
    <br>
        <input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
        <br>
        <input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
        <br>
        <input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
        <br>
        <input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
        <br>
        <input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/></font>
</p>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter NEW Value Card number here:
		<input type="text" name="newpc" maxlength="8"/>
	</font>
</p>
<p>
	<font face="Verdana, Arial, Helvetica, sans-serif"> <br />
	<input type="submit" name="Submit2" value="Transfer the Values" onclick="return confirm(\\'Are you sure you want to transfer the Punches and Points from \\'+document.form2.oldpc.value+\\' to \\'+document.form2.newpc.value+\\'?\\')"/>
	</font>
</p>
</form>
';

else 
$transfer_box = $message;

$search_box = '
<form name="form1" id="form1" method="post" action="value_transfertest.php">
<input type="hidden" name="action" value="search">
<p>
	<font size="1" face="Verdana, Arial, Helvetica, sans-serif">Optional: </font>
	<font size="3" face="Verdana, Arial, Helvetica, sans-serif">LOOK UP A CARD NUMBER <font size="2">(Enter 2 of the following):</font></font>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Phone Number: </font>
	<input type="text" name="data[phone]" value="'.stripslashes($DATA[phone]).'"/>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Last Name: </font>
	<input type="text" name="data[last]" value="'.stripslashes($DATA[last]).'"/>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Email Address: </font>
	<input type="text" name="data[email]" value="'.stripslashes($DATA[email]).'"/>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">
	<input type="submit" name="Submit" value="Look up card" />
	</font>
	
</p>
</form>
';

[adrian_schmidt]

Oops… I had an error in my code-example…

<p>
    <font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter OLD Value Card number here:
    <br>
    <?php
    while ($row = mysql_fetch_array($result)) {
        $CARD = $row['pc'];
        ?>
        <input type="text" name="oldpc[]" value="<?php echo $CARD['pc']; ?>" /><br />
        <?php
    }
    ?>
</font>
</p>

should be

<p>
    <font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter OLD Value Card number here:
    <br>
    <?php
    while ($row = mysql_fetch_array($result)) {
        ?>
        <input type="text" name="oldpc[]" value="<?php echo $row['pc']; ?>" /><br />
        <?php
    }
    ?>
</font>
</p>

[adrian_schmidt]

What error is that? Could you post the error message please?

Are you looping through the result twice?

Quote from the PHP manual:

Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.

This means that if you already looped through the results once, you can’t do it again. (You can probably reset the pointer, but I don’t know how from the top of my mind.)

So, if you’re still using that loop { print_r() } to debug, then try removing that.

[wakk0]

keeps giving me the mysql_fetch_array() error…but when i did a echo on my sql statement my form doesn’t work anymore

<?php
require('../inc/header.inc.php');
//require('../inc/auth.inc.php');
//require("includes/defaults.php");
require("includes/defaultstest.php");

$loginuser = $_SESSION["SESS_username"];


$db = new Database(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_TABLE_PREFIX);
$db->connect(); // connect to the server

$MSG = $_REQUEST['msg'];

$ACTION = $_REQUEST['action'];

switch ($ACTION) {
	case 'search':
		$DATA = $_REQUEST['data'];

		if($DATA['phone']) $WHERE_CLAUSE[] = "phone='".$db->escape($DATA['phone'])."'"; else unset($DATA['phone']);
		if($DATA['last']) $WHERE_CLAUSE[] = "last='".$db->escape($DATA['last'])."'"; else unset($DATA['last']);
		if($DATA['email']) $WHERE_CLAUSE[] = "email='".$db->escape($DATA['email'])."'"; else unset($DATA['email']);

		if(count($DATA)>=2){
			$WHERE_CLAUSE = implode(' AND ', $WHERE_CLAUSE);
			switch ($ACTION) {
				case 'search':

//need to add here:
					$sql = "
						SELECT * FROM card WHERE register='1' AND $WHERE_CLAUSE
					";
					$CARD = $db->query_first($sql);
					if(!$CARD) $message = 'There is no card number associated with the information you provided';
				break;
			}
		} else {
			$message = 'Minimum two fields are required';
		}
	break;
	case 'transfer':
		
		if($_REQUEST['oldpc'] && $_REQUEST['newpc']){
		
			$OLDPC = $_REQUEST['oldpc'];

			//print_r ($OLDPC);
			//$array_of_all_oldpc = explode(',', $OLDPC); 
           //  print_r ($array_of_all_oldpc);
			
			$NEWPC = $_REQUEST['newpc'];
			$today = date("Y-m-d"); 

			/* Get old card data - including points and punch values */
	   for($no=0;$no<count($_REQUEST['oldpc']);$no++){

			$sql = "
				SELECT card.pc, card.active_date, card.first, card.last, card.email, card.phone, card.register, card.forcexport, points.points, punch.punchval FROM card
					LEFT JOIN points ON points.pc = card.pc
					LEFT JOIN punch ON punch.pc = card.pc
				WHERE card.pc ='".$_REQUEST['oldpc'][$no]."'";
			
			$OLD_DATA = $db->query_first($sql);
		
			if(!$OLD_DATA[points]) $OLD_DATA[points] = 0;
			if(!$OLD_DATA[punchval]) $OLD_DATA[punchval] = 0;

			/* Update new card data - including points and punch values */
		   $result = mysql_query($sql);
           if(!$result){$error.= 'Query failed: '.mysql_error();}

        // IF THERE ARE NO RESULTS DISPLAY MESSAGE
        $fetch_row = mysql_fetch_row($result);
        $numrows = $fetch_row[0];
		$transferold = "$today, $OLD_DATA[pc], $loginuser, $OLD_DATA[punchval], $OLD_DATA[points]";
        if($numrows==0)
		
        {
		 echo '<center><font color=red><h3>The card number "'.$value.'"  doesn\\'t exist.</h3></font></center>';
		}
		
		elseif($_REQUEST['value'] == $_REQUEST['newpc']){
			 echo '<center><font color=red><h3>ERROR: The same card number was typed in both fields.</h3></font></center>';
		}
		
		else {
			
			if ($OLD_DATA['register'] == '0') {
			// $query = "INSERT INTO logs(username,activity) VALUES(`$username`,`$currentDate`) ON DUPLICATE KEY UPDATE activity=`$currentDate`";
//$go = mysql_query($query);
            $sql = "INSERT INTO card (pc, transfrom, forcexport, transdate)
VALUES ('$NEWPC', '$transferold', '1','$today')
ON DUPLICATE KEY UPDATE
forcexport = '1', transfrom='$transferold', transdate='$today'";
		
						$db->query($sql);
			}
			else {
				
			$sql = "
				INSERT INTO card (pc, active_date, first, last, email, phone, register, transfrom, forcexport, transdate) 
				VALUES (".$NEWPC.", '".$OLD_DATA['active_date']."', '".$OLD_DATA['first']."', '".$OLD_DATA['last']."', '".$OLD_DATA['email']."',                       '".$OLD_DATA['phone']."', '".$OLD_DATA['register']."', '$transferold', '1','$today') 
				ON DUPLICATE KEY UPDATE active_date ='".$OLD_DATA['active_date']."', first='".$OLD_DATA['first']."', last='".$OLD_DATA['last']."', email='".$OLD_DATA['email']."', phone='".$OLD_DATA['phone']."', register=1, transfrom='$transferold', forcexport=1, transdate='$today'";
			
				 }
			$db->query($sql);
		}

			$sql = "
				INSERT INTO points (pc, points) 
				VALUES ('$NEWPC', $OLD_DATA[points]) 
				ON DUPLICATE KEY UPDATE points = points + $OLD_DATA[points]
			";
			$db->query($sql);
			
		
			$sql = "
				INSERT INTO punch (pc, punchval) 
				VALUES ('$NEWPC', $OLD_DATA[punchval]) 
				ON DUPLICATE KEY UPDATE punchval = punchval + $OLD_DATA[punchval]
			";
			$db->query($sql);
			
			
		
			$sql = "
				UPDATE points set points='0' WHERE pc=$OLD_DATA[pc] 
			";
			$db->query($sql);
			
			$sql = "
				UPDATE punch set punchval='0' WHERE pc=$OLD_DATA[pc]
			";
			$db->query($sql);
		
			
			$transfered = "$today, $NEWPC, $loginuser, $OLD_DATA[punchval], $OLD_DATA[points]";
			
			$sql = "
				UPDATE card SET register='$OLD_DATA[register]',transfered='$transfered', transdate='$today' WHERE pc='$OLD_DATA[pc]'
			";
			$db->query($sql);
			
			if(!$db->error) header("Location: value_transfer.php?msg=success"); else $MSG = 'error';
		   
		} 
	} //end foreach
		else {
			
			$message = 'Both "oldpc" and "newpc" fields are required!';
			
		}
		
	break;
}

$db->close(); // close connection

//=============================================================================================================================//

switch($MSG){
	case 'success':
		$MSG_TEXT = '<font color="#15d505">Database successfully updated!</font>';

	break;
	case 'error':
		$MSG_TEXT = '<font color="#FF0000">Database has not been updated!</font>';
	break;
}

//=============================================================================================================================//

if(!$message)
$transfer_box = '
<form name="form2" id="form2" method="post" action="value_transfer.php" onSubmit="return ValidateForm()">
<input type="hidden" name="action" value="transfer" />
<p>
	<font size="3" face="Verdana, Arial, Helvetica, sans-serif">TRANSFER</font>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter OLD Value Card number here:

//need to add loop here!
	<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/></font>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter NEW Value Card number here:
		<input type="text" name="newpc" maxlength="8"/>
	</font>
</p>
<p>
	<font face="Verdana, Arial, Helvetica, sans-serif"> <br />
	<input type="submit" name="Submit2" value="Transfer the Values" onclick="return confirm(\\'Are you sure you want to transfer the Punches and Points from \\'+document.form2.oldpc.value+\\' to \\'+document.form2.newpc.value+\\'?\\')"/>
	</font>
</p>
</form>
';
else
$transfer_box = $message;
 
$search_box = '
<form name="form1" id="form1" method="post" action="value_transfer.php">
<input type="hidden" name="action" value="search">
<p>
	<font size="1" face="Verdana, Arial, Helvetica, sans-serif">Optional: </font>
	<font size="3" face="Verdana, Arial, Helvetica, sans-serif">LOOK UP A CARD NUMBER <font size="2">(Enter 2 of the following):</font></font>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Phone Number: </font>
	<input type="text" name="data[phone]" value="'.stripslashes($DATA[phone]).'"/>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Last Name: </font>
	<input type="text" name="data[last]" value="'.stripslashes($DATA[last]).'"/>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Email Address: </font>
	<input type="text" name="data[email]" value="'.stripslashes($DATA[email]).'"/>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">
	<input type="submit" name="Submit" value="Look up card" />
	</font>
	
</p>
</form>
';


//=============================================================================================================================//
?>

[adrian_schmidt]

@wakk0

<p>
    <font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter OLD Value Card number here:
    <br>
    
        <input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
        <br>
        <input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
        <br>
        <input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
        <br>
        <input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
        <br>
        <input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/></font>
</p>

Just as you have a loop here:

$result = mysql_query("SELECT * FROM card WHERE register='1' AND $WHERE_CLAUSE");
while ($row = mysql_fetch_array($result)) {
$CARD = $row['pc'];
print_r($CARD);
}

You should have a loop like this:

<p>
    <font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter OLD Value Card number here:
    <br>
    <?php
    while ($row = mysql_fetch_array($result)) {
        $CARD = $row['pc'];
        ?>
        <input type="text" name="oldpc[]" value="<?php echo $CARD['pc']; ?>" /><br />
        <?php
    }
    ?>
</font>
</p>

I’ve never seen a name for an input field be something like “oldpc[]”. I’m not sure how that is supposed to work? It looks like you’re trying to make an array here? Maybe that works and I’m just not familiar with it, otherwise you could make an incrementing variable $i and have the name be “oldpc<?php echo $i; ?>”. That will name your inputs “oldpc0”, “oldpc1”, “oldpc2” etc. Just remember to increment the value of $i at the end of the loop!

[wakk0]

no, there isn’t a loop in my code…i don’t know where to put it to make it work correctly with my existing code…
this is what i have:

  $result = mysql_query("SELECT * FROM card WHERE register='1' AND $WHERE_CLAUSE");
while ($row = mysql_fetch_array($result)) {
$CARD = $row['pc'];
print_r($CARD);
}

which works correctly but i would like have the results display here:

$transfer_box = '
<form name="form2" id="form2" method="post" action="value_transfertest.php" onSubmit="return ValidateForm()">
<input type="hidden" name="action" value="transfer" />
<p>
	<font size="3" face="Verdana, Arial, Helvetica, sans-serif">TRANSFER</font>
</p>
<p>
	<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Enter OLD Value Card number here:
	<br>
	
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/>
		<br>
		<input type="text" name="oldpc[]" value="'.$CARD['pc'].'"/></font>
</p>