adding further conditions to mysql loop

Question

<?php
$query = $_GET['query'];
// gets value sent over search form

$min_length = 6;
// you can set minimum length of the query if you want

if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then

$query = htmlspecialchars($query);
// changes characters used in html to their equivalents, for example: < to &gt;

$query = mysql_real_escape_string($query);
// makes sure nobody uses SQL injection

$raw_results = mysql_query("SELECT * FROM cwnational WHERE (`postcode` = '$query') OR (`structure` LIKE '%".$query."%')") or die(mysql_error());

if(mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following


while($results = mysql_fetch_array($raw_results)){

echo "<p><h3>".$results['postcode']."</h3>".$results['structure']."</p>";



}

while($results = mysql_fetch_array($raw_results)){

if($results['structure'] = "National") {

echo "print national_table";

} else {
echo "print local_table";



}



}
else{ // if there is no matching rows do following
echo "No results";
        }

    }
    else{ // if query length is less than minimum
        echo "Minimum length is ".$min_length;
    }


?>
</body>

I'm totally stumped now..

When I successfully match a $query, I want to use the 2nd part of the array which should be a column called structure and use that as a switch to either print table_local or table_national from the db.

re-wrote it after getting to grips with the correct approach,

if (empty($searchTerm)) {
                echo "<h1>Empty search term</h1>";
                $sqlQuery = false;
            } elseif (strlen($searchTerm) < $minQueryLength) {
                echo "<h1>Search term must be at least ".$minQueryLength." characters long.";
                $sqlQuery = false;
            } else {
                if (strlen($firstPart) + strlen($secondPart) == 7) {
                    $sqlQuery = $mysqli->query("SELECT `postcode`, `structure` FROM `cwnational` WHERE `postcode` LIKE '".$firstPart." ".$secondPart."'");
                } else {
                    $sqlQuery = $mysqli->query("SELECT `postcode`, `structure` FROM `cwnational` WHERE REPLACE(`postcode`, ' ', '') LIKE '".$searchTerm."%'");
                }
            }

            if (is_object($sqlQuery) && $sqlQuery->num_rows >= 1) {
                $resultArr = array();
                while($row = $sqlQuery->fetch_assoc()) {
                    $resultArr[$row['postcode']] = array();
                    $priceQuery = $mysqli->query("SELECT `base_rate`, `commit_mbps` FROM `pricing` WHERE `structure` = '".$row['structure']."'");
                    if ($priceQuery->num_rows >= 1) {
                        while ($price = $priceQuery->fetch_assoc()) {
                            $resultArr[$row['postcode']][$price['commit_mbps']] = ((float)$price['base_rate'] + $transit[$price['commit_mbps']] ) + ( ( $transit[$price['commit_mbps']] + (float)$price['base_rate'] ) * ((float)$apiUser['margin']/100)  )   ;
                        }
                    }
                }

as a noob it's not too late to avoid learning bad habits, you should start with mysqli — Joe T
– Joe T, Commented Jul 2, 2013 at 22:46
hi joe t, thanks for the input, I'll take that into consideration, can you point me in the right direction for this exercise though please? — Will Stocker
– Will Stocker, Commented Jul 2, 2013 at 22:47

user1864610 · Accepted Answer · 2013-07-02 22:55:36Z

2

You're reading the result set twice. This will work for the first loop, but won't execute the second because you've already reached the end. If you need to read the results twice, use this:

mysql_data_seek ($raw_results , 0 )

immediately before the second loop to set the pointer to the beginning again.

Of course, you should be using mysqli...

answered Jul 2, 2013 at 22:55

user1864610

Sign up to request clarification or add additional context in comments.

Comments

Joe T · Accepted Answer · 2013-07-02 22:55:06Z

1

what's going wrong now? I see you need to add an equals sign here:

if($results['structure'] == "National") {

note: double equals for php conditional

It also looks like you have an "else" coming out of a "while", that won't work. And you did this "while" loop twice, I'd combine them into a single "While":
while($results = mysql_fetch_array($raw_results)){

edited Jul 2, 2013 at 22:55

answered Jul 2, 2013 at 22:47

Joe T

2,3601 gold badge19 silver badges34 bronze badges

Collectives™ on Stack Overflow

adding further conditions to mysql loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related