After a bit of help with PHP

Linken

Linken

Linken

Associate
Joined
10 May 2007
Posts
541
Location
Kent
Hey guys,

Am trying to make a script for work to save us some time and I'm having trouble. I'm trying to pull data from a text area for multiple SQL Select, and then display the data.

I'm at a stand still as I've been trying for the good part of 5-6 hours to get it working. As you can see, just a basic form / display, but it only displays the first number. And then has the first 2-3 sets of numbers in the array but doesnt display them properly. I'm going to guess its a silly error but I'm pretty tired ATM. If any of you could spot it I'd be in your debt :)

2cfcu81.jpg

Code: 
require("dompdf-0.5.1/dompdf_config.inc.php");
require("include/config.php");
if (isset($_POST['textarea'])) {
           $text = explode("\n", $_POST['textarea']);
for ( $i=0, $size = count($text); $i<$size; $i++ )
{
	$mydata = $text[$i];
	$sql = "SELECT * FROM company WHERE AccID= '$mydata'";
	$result = mysql_query($sql);
	echo "<table border='1'><tbody><tr>\n";
// Acc Id //
echo '<th>
Acc No
</th>';

// Company Name //
echo '<th>
Company Name
</th>';

// E-Mail // 
echo '<th>
E-mail
</th>';

// Acc Manager //
echo '<th>
Acc Manager
</th>';

// PDF //
echo '<th>
PDF
</th>';

/// Search Results Displayed ///
   if ($text !=NULL) {echo "<h3><font face='Verdana'>Search results for $text[$i]:</h3></face>"; }
   for( $i = 0; $row = mysql_fetch_array($result); $i++ )
{
  echo "<tr>";
  echo '<td><a href=companyinfo.php?AccID='  .$row['AccID'] . '>' .$row['AccID'] . '</a></td>';
  echo "<td>"  .$row['Customer_Name'] . "</td>";  
  echo '<td><a href=mailto:'.$row['Email']. '>'.$row['Email']. '</a></td>';
  echo "<td>"  .$row['Acc_Manager'] . "</td>";
  echo '<td>PDF:<a href=http://myurl.dompdf-0.5.1/dompdf.php?base_path=..%2Fdompdf-0.5.1%2Fwww%2Ftest%2Fvoucher.php&input_file=..%2Fdompdf-0.5.1%2Fwww%2Ftest%2Fvoucher.php>'.$row['Customer_Name']. '</a></td>';
  echo "</tr>";
}
echo "</tbody></table>";
		
			  }
exit();}
 
Your using the variable i inside a loop which is also using i. Use j or something else in the nested for loop. Alternatively replace the second for loop with "while ($row = mysql_fetch_array($result)){}". As a side note, you should always ue the count function outside for loops as its evaluated for every iteration of the loop which is detrimental to performance.
 
Last edited:
I think I have far too much time... here's a re-written example. It should ward against dodgy input and because I'm anal it's XHTML strict compliant :o.

Results:
results.png


Code:
PHP: 
<?php
// Form submitted?
if ( $_POST['ids'] ) {
	
	// Split IDs by new line
	$arrIDs = explode("\r\n", trim($_POST['ids']));
	
	// Filter through the list of IDs to remove bad entries
	// assume only numbers allowed
	$arrFilteredIDs = Array();
	foreach ( $arrIDs as $ID )
	{
		if ( is_numeric($ID) )	
			array_push($arrFilteredIDs,$ID);
	}
	
	// Convert IDs into a comma separated list for MySQL
	$IDs = implode(",", $arrFilteredIDs);
	
	// Connect to mySQL - use your own settings etc here
	$link = mysql_connect ("localhost", "root", "ilikehorses") or die (mysql_error());
	mysql_select_db ("test", $link) or die (mysql_error($link));
	
	// Execute query - sprintf just allows us to format strings nicely
	// uses mysql_real_escape_string to prevent naughty things
	$mysql_result = mysql_query (
		sprintf("SELECT * FROM company WHERE AccID IN (%s) ORDER BY AccID", mysql_real_escape_string($IDs, $link)), $link) or die (mysql_error($link)
	);
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
    <meta http-equiv="Content-type" content="text/html; charset=utf-8" />
    <meta http-equiv="Content-Language" content="en-us" />

    <title>Meh</title>
	
	<style media="all" type="text/css">
	h1 {
		font-size: 1.4em;
	}
	fieldset {
	width: 165px;
	}
	textarea {
		background-color:#FFFFAA;
		height: 120px;	
		width: 150px;
	}
	table
	{
		border-color: #600;
		border-width: 0 0 1px 1px;
		border-style: solid;
	}

	th,td
	{
		border-color: #600;
		border-width: 1px 1px 0 0;
		border-style: solid;
		padding: 4px;
		background-color: #FFC;
		width:200px;
	}
	</style>
</head>

<body>

<form method="post" action="">
<h1>Search</h1>
	<fieldset>
		<legend>Multi ID search</legend>
		<p><label for="ids">IDs (one per line):</label></p>
		<textarea id="ids" name="ids" rows="8" cols="16"><?php if ($_POST['ids']) echo trim($_POST['ids']); ?></textarea>
		
		<div class="clear"><input type="submit" value="submit" /></div>
	</fieldset>
</form>

<?php if ($_POST['ids']) { ?>
	<h1>Search Results</h1>
	<table>
	<tr>
	<th>Acc No</th>
	<th>Company Name</th>
	<th>E-Mail</th>
	<th>Account Manager</th>
	<th>PDF</th>
	</tr>

	<?php
		// Get results
		while ($temp = mysql_fetch_array ($mysql_result)) {
	?>
		<tr>
			<td><a href="companyinfo.php?AccID=<?php echo $temp['AccID']?>"><?php echo $temp['AccID']?></a></td>
			<td><?php echo $temp['Customer_Name']; ?></td>
			<td><?php echo $temp['Email']; ?></td>
			<td><?php echo $temp['Acc_Manager']; ?></td>
			<td>Code it yourself :p</td>
		</tr>
	<?php
		}
	?>

	</table>
<?php } ?>
</body>
</html>


Let me know if you want anything explaining.
 
Ladforce: Ah, see that now. Will remember for future. Thanks :)

Pho: Just went through the code, amazing mate. Can't believe how quick it is as well with my database with ~100,000 records. I have got the PDF export to work aswell now, was pretty tricky but seems to be working as intended. One thing though I have found atm is that I don't think it says which AccID's is doesn't find. Is it possible to show if it doesn't find any? That would be extremely helpful.

Cheers mate, thx a ton.
 
Last edited:
Pho said:
I think I have far too much time... here's a re-written example. It should ward against dodgy input and because I'm anal it's XHTML strict compliant :o.
Click to expand...

You deserve kudos for spending the time and effort to do all of that! :)
 
zpos said:
You deserve kudos for spending the time and effort to do all of that! :)
Click to expand...

It's always good to keep my PHP skills up to scratch as I don't use it much at the moment :D.

Linken said:
Pho: Just went through the code, amazing mate. Can't believe how quick it is as well with my database with ~100,000 records. I have got the PDF export to work aswell now, was pretty tricky but seems to be working as intended. One thing though I have found atm is that I don't think it says which AccID's is doesn't find. Is it possible to show if it doesn't find any? That would be extremely helpful.
Click to expand...

The reason it's much quicker is that you were executing a new query for each search string, i.e. if I searched for:

1111
2222
3333
...
nnnn

You would run:
SELECT * FROM Company WHERE AccID = 1111
SELECT * FROM Company WHERE AccID = 2222
SELECT * FROM Company WHERE AccID = 3333
...
SELECT * FROM Company WHERE AccID = nnnn

And so it would have to go and search over all your records for every AccID you entered (unless you had indexes set-up on the AccID column in your database, which should speed it up even more).

Using IN we can pack it all into one query, so it now becomes:
SELECT * FROM Company WHERE AccID IN (1111,2222,3333,nnnn)

Which (I think, I don't know the inner workings of it) would only have to parse your records once.




I thought listing what it hadn't found when I was writing it but didn't get around to doing it. This probably isn't the best way, but try this:

PHP: 
<?php
// Form submitted?
if ( $_POST['ids'] ) {
	
	// Split IDs by new line
	$arrIDs = explode("\r\n", trim($_POST['ids']));
	
	// Filter through the list of IDs to remove bad entries
	// assume only numbers allowed
	$arrFilteredIDs = Array();
	foreach ( $arrIDs as $ID )
	{
		if ( is_numeric($ID) )	
			array_push($arrFilteredIDs,$ID);
	}
	
	// Convert IDs into a comma separated list for MySQL
	$IDs = implode(",", $arrFilteredIDs);
	
	// Connect to mySQL - use your own settings etc here
	$link = mysql_connect ("localhost", "root", "ilikehorses") or die (mysql_error());
	mysql_select_db ("test", $link) or die (mysql_error($link));
	
	// Execute query - sprintf just allows us to format strings nicely
	// uses mysql_real_escape_string to prevent naughty things
	$mysql_result = mysql_query (
		sprintf("SELECT * FROM company WHERE AccID IN (%s) ORDER BY AccID", mysql_real_escape_string($IDs, $link)), $link) or die (mysql_error($link)
	);
	
	// Found IDs in database array
	$arrFoundIDs = Array();
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
    <meta http-equiv="Content-type" content="text/html; charset=utf-8" />
    <meta http-equiv="Content-Language" content="en-us" />

    <title>Meh</title>
	
	<style media="all" type="text/css">
	h1 {
		font-size: 1.4em;
	}
	fieldset {
	width: 165px;
	}
	textarea {
		background-color:#FFFFAA;
		height: 120px;	
		width: 150px;
	}
	table
	{
		border-color: #600;
		border-width: 0 0 1px 1px;
		border-style: solid;
	}

	th,td
	{
		border-color: #600;
		border-width: 1px 1px 0 0;
		border-style: solid;
		padding: 4px;
		background-color: #FFC;
		width:200px;
	}
	</style>
</head>

<body>

<form method="post" action="">
<h1>Search</h1>
	<fieldset>
		<legend>Multi ID search</legend>
		<p><label for="ids">IDs (one per line):</label></p>
		<textarea id="ids" name="ids" rows="8" cols="16"><?php if ($_POST['ids']) echo trim($_POST['ids']); ?></textarea>
		
		<div class="clear"><input type="submit" value="submit" /></div>
	</fieldset>
</form>

<?php if ($_POST['ids']) { ?>
	<h1>Search Results</h1>
	<table>
	<tr>
	<th>Acc No</th>
	<th>Company Name</th>
	<th>E-Mail</th>
	<th>Account Manager</th>
	<th>PDF</th>
	</tr>

	<?php
		// Get results
		while ($temp = mysql_fetch_array ($mysql_result)) {
			// Save the list of found account IDs
			array_push($arrFoundIDs, $temp['AccID']);
	?>
		<tr>
			<td><a href="companyinfo.php?AccID=<?php echo $temp['AccID']?>"><?php echo $temp['AccID']?></a></td>
			<td><?php echo $temp['Customer_Name']; ?></td>
			<td><?php echo $temp['Email']; ?></td>
			<td><?php echo $temp['Acc_Manager']; ?></td>
			<td>Code it yourself :p</td>
		</tr>
	<?php
		}
	?>

	</table>
	
	<h1>Unfound IDs</h1>
	<table>
	<tr>
	<th>Acc No</th>
	</tr>
	<?php
		// Show unfound IDs
		foreach ($arrFilteredIDs as $ID) {
			if (!in_array($ID, $arrFoundIDs)) {
			?>
				<tr>
					<td><?php echo $ID; ?></td>
				</tr>
			<?php
			}
		}
	?>
<?php } ?>
</body>
</html>


Bits added:

Code: 
/* LINE 30 */
// Found IDs in database array
$arrFoundIDs = Array();

/* LINE 101 */
// Save the list of found account IDs
array_push($arrFoundIDs, $temp['AccID']);

/* LINE 117 */
<h1>Unfound IDs</h1>
<table>
<tr>
<th>Acc No</th>
</tr>
<?php
	// Show unfound IDs
	foreach ($arrFilteredIDs as $ID) {
		if (!in_array($ID, $arrFoundIDs)) {
		?>
			<tr>
				<td><?php echo $ID; ?></td>
			</tr>
		<?php
		}
	}
?>
 
Thanks a lot man, just added this in. Works great :)

I'm just touching up a few things with this PDF export and should be good to go!
 
Nice, glad it works :).

I didn't even attempt to understand your PDF code, the url looked quite complex :o.
 
You must log in or register to reply here.
Back
Top Bottom