IF Else and While, selecting specific data from a DB.

[rattlsnak]

OK, username says it all, But I'm trying to learn, not just look for a quick answer.here we go......On one of my pages I have a search form with three available options to search (with their own searchfields, same form):Lastname________Team________Division_____[submit]I have used the follwing code (see below) to perform a search depending on which textfield they enter data into. It works fine when they enter a value that is in the database, but the problem I'm having is:A: If they enter something into the textfield that isn't in the database, the page appears blank. (nothing to echo) Because of the code I have there, it is obviously simply looking for something in the textfield, not whether it's in the DB.How can I correct this? I have a page that says "no results found" but cant get it to redirect there.B. I have more than one person with the same last name and need to display them all if requestes, but i cant seem to get the WHILE statement to work either. Is that the correct loop to use?C. I also eventually want it to redirect to another 'pretty' page to display the results, but not sure where to put that header.D. Is there a way to echo all those statements in a single place instead of repeating them? I guess I could assisn the string a variable and do it that way?**I have changed the names of the variables and such to protect the innocent! $query1="SELECT * FROM bandits WHERE l_name='$l_name'";$query2="SELECT * FROM bandits WHERE team='$team'";$query3="SELECT * FROM bandits WHERE div='$division'";if ($l_name) {$results=mysql_query($query1) or die (mysql_error());$row = mysql_fetch_array($results);echo $row['l_name'];echo $row['team'];echo $row['div'];}elseif ($team) {$results=mysql_query($query2) or die (mysql_error());$row = mysql_fetch_array($results);echo $row['l_name'];echo $row['team'];echo $row['div'];}elseif ($division) {$results=mysql_query($query3) or die (mysql_error());$row = mysql_fetch_array($results);echo $row['l_name'];echo $row['team'];echo $row['div'];}else { header("location:noresultsfound.php");}?>

[justsomeguy]

You should be using mysql_real_escape_string to sanitize your variables and avoid SQL injection attacks if you're not already. It's not a big deal for a search form, but it's good practice.$query1="SELECT * FROM bandits WHERE l_name='" . mysql_real_escape_string($l_name) . "'";The way the code is set up, it should be doing one of the three blocks and outputting something, or else redirecting. You might want to change your conditions to use empty to check the variables, and have some output that just tells you what the code is doing.if (!empty($l_name)){echo "found last name";...I'm not sure how sensitive PHP or the browser is with headers, but when I send a location header I capitalize Location and have a space between the colon and the URL.header("Location: noresultsfound.php");If you have more than one result then a while loop is the best way to go.

$results=mysql_query($query1) or die (mysql_error());while ($row = mysql_fetch_array($results)){  echo $row['l_name'];  echo $row['team'];  echo $row['div'];  echo '<br>';}

D. Is there a way to echo all those statements in a single place instead of repeating them? I guess I could assisn the string a variable and do it that way?

if ($l_name) {  $results=mysql_query("SELECT * FROM bandits WHERE l_name='$l_name'") or die (mysql_error());}elseif ($team) {  $results=mysql_query("SELECT * FROM bandits WHERE team='$team'") or die (mysql_error());}elseif ($division) {  $results=mysql_query("SELECT * FROM bandits WHERE div='$division'") or die (mysql_error());}else {   header("Location: noresultsfound.php");  exit();}if (mysql_num_rows($results) == 0)  echo "no results";while ($row = mysql_fetch_array($results)){  echo $row['l_name'];  echo $row['team'];  echo $row['div'];  echo '<br>';}

[rattlsnak]

You should be using mysql_real_escape_string to sanitize your variables and avoid SQL injection attacks if you're not already. It's not a big deal for a search form, but it's good practice.$query1="SELECT * FROM bandits WHERE l_name='" . mysql_real_escape_string($l_name) . "'";The way the code is set up, it should be doing one of the three blocks and outputting something, or else redirecting. You might want to change your conditions to use empty to check the variables, and have some output that just tells you what the code is doing.if (!empty($l_name)){echo "found last name";...I'm not sure how sensitive PHP or the browser is with headers, but when I send a location header I capitalize Location and have a space between the colon and the URL.header("Location: noresultsfound.php");If you have more than one result then a while loop is the best way to go.

$results=mysql_query($query1) or die (mysql_error());while ($row = mysql_fetch_array($results)){  echo $row['l_name'];  echo $row['team'];  echo $row['div'];  echo '<br>';}

if ($l_name) {  $results=mysql_query("SELECT * FROM bandits WHERE l_name='$l_name'") or die (mysql_error());}elseif ($team) {  $results=mysql_query("SELECT * FROM bandits WHERE team='$team'") or die (mysql_error());}elseif ($division) {  $results=mysql_query("SELECT * FROM bandits WHERE div='$division'") or die (mysql_error());}else {   header("Location: noresultsfound.php");  exit();}if (mysql_num_rows($results) == 0)  echo "no results";while ($row = mysql_fetch_array($results)){  echo $row['l_name'];  echo $row['team'];  echo $row['div'];  echo '<br>';}

That did it! Thanks.. I would have never thought to do it that way at first, and ive relaized quickly in PHP there is always more than several ways to accomplish something.As far as the escape string, Ive been using those as well as the stripslashes .

[rattlsnak]

I also have a question about importing an XLS file over, eerytime i try, i keep getting an invalid field count error.I have 12 fields in my table, and have made the .xls file indentical to it. I am saving it as a CSV file, and when I import it, at some random point, it says error, invalid field count in CSV at XX line. Do I need to change the field seprators or field terminators in the import screen to something other than ; or auto, or? I always get the message at the end aying the error is on the next line past what i have.Thanks..EDIT:OK, I put a comma in the field sperator and it worked...thanks..

[rattlsnak]

Back to the original issue,.. This code doesnt seem to be doing anything:else { header("Location: noresultsfound.php"); exit();}No matter what i type in the fields, if it is not a match, or left blank, it goes to this: (which is fine)if (mysql_num_rows($results) == 0) echo "no results";So my question is this: When you are using, IF ELSEIF, does there have to be an ending ELSE? Can I simply remove the last ELSE, and still have the script run OK? And if that is OK, then I would move the (location:) to the last IF and that should redirect, correct?Basically what I am after, is to have the search redirect to the no results page instead of the echo command, and to have the correct results redirect to another page also, but I cant get it to work right.TIA

[justsomeguy]

An if statement doesn't require an else. You can replace the echo statement for no results with a redirect if you want to. Since it's not running the else to redirect if none of them were filled in, it might be useful to add an echo to each if statement so that you know which one is succeeding to make it not get to the else.

if ($l_name) {  echo 'found l_name: ' . $l_name;  $results=mysql_query("SELECT * FROM bandits WHERE l_name='$l_name'") or die (mysql_error());}elseif ($team) {  echo 'found team: ' . $team;  $results=mysql_query("SELECT * FROM bandits WHERE team='$team'") or die (mysql_error());}elseif ($division) {  echo 'found division: ' . $division;  $results=mysql_query("SELECT * FROM bandits WHERE div='$division'") or die (mysql_error());}else {   header("Location: noresultsfound.php");  exit();}

I'm not sure why you're doing all the redirects though, you can have one page handle everything. Keep in mind that if you redirect you lose anything from $_POST.

[rattlsnak]

After thinking about it some more, I do still want a redirect to go to a noresults page because it has the same styling/theme as the rest of the site with the other links etc on it, instead of simply returning 'no results', and I got that part working ok by replacing the echo statement with a header command. BUT, I had to add the header command to the last IF, it will not work behind the last ELSE as you have it there. Basically, it either has correct data or it was echo no results found. So the last ELSE never came into play. Maybe it would work if we did a ELSE (! l_name && ! $team && ! div) etc., but then that would only work if nothing is entered into the fields, not if it had wrong data. I guess there is really no need to for it to go anywhere else besides the noresults page if it cant find any data or the user doesnt inout any data at all, the end result is still, no results found!if (mysql_num_rows($results) == 0){////echo "no search results found!";header("location:noresultsfound.php");exit;}How can I display the results on another page? (searchresults.php) Right now I have built a table to display the data in: echo "<table border='1'><tr><th>Last Name</th><th>Team</th><th>Division</th></tr>";while ($row = mysql_fetch_array($results)) { echo "<tr>"; echo "<td>" . $row['l_name'] . "</td>"; echo "<td>" . $row['team'] . "</td>"; echo "<td>" . $row['div'] . "</td>"; echo "</tr>"; }echo "</table>";Im getting crossed between regular html and php. Too many brain cells being merged! The layout I have been using is basically a search input page, which posts to a checksearch input page, which has no html on it, which if correct, displays onto itself, if not, goes to the noresults page. I was figuring I could display the results on a designed/formatted page by doing a redirect, but I guess I could simply use the same page and style it. Bu at that point, the php table code would have to go into the page after the html. Is that right?After rereading my post, Im assuming I could use the same styled page to display either no results, or the correct data based on what is entered, is that correct? If so, I wouldnt know where to begin! I know I have a ton of questions, and certainly appreciate the help!

[justsomeguy]

It's always best if you can separate your PHP and HTML code. A template system would work wonders for you. To start simple, a lot of people just include the files they want to use. So you would have a file that, for example, has the beginning of the HTML code, the <head> section, the start of <body>, and maybe a page header and navigation menu or something. Then another file that has the page footer and closes the body and html tags. So your page would include the header file, then output its content (which would pick up whatever formatting is defined in the header), then include the footer.

include 'header.php';echo '<table>';...include 'footer.php';

That way you don't have the same code in all your files, it's only in one file that you can modify to change every page. So that's a simple way to do this, you can expand on that and use a full-blown template system. In my pages, I have a class defined called Page that I use to output everything. I send the Page class all of the dynamic data and tell it what template to use for the page, and it takes care of the rest, actually putting the data where it goes in the template and outputting the HTML code. So the code to display the home page might look something like this:

<?phprequire 'global.conf.php'; // will include the Page class$page = new Page();$page->set_template('home.tpl');$content = array();$content['page_title'] = 'Home';$content['logged_in'] = false;$content['records'] = array(  array('name' => 'Joe', 'age' => 30),  array('name' => 'Jill', 'age' => 25));$page->process($content);$page->output();?>

So there's no HTML at all there, you're just sending data to the page class which will put together the page and display it. Ideally that's the way to go, then you don't need to go around redirecting for everything, you just load a different template.

[rattlsnak]

Awesome, i understand.. I am using include files now for all mysql_connect scripts, etc, just never thought to use them that way! duh!Thanks again, I'm sure I'll have alot more questions!

[justsomeguy]

Since I don't work on Fridays and I'm home, I've got access to my classes if you want to use them. I have a page, database, and session class that make my development a lot faster. Using these classes, my login page only needs to contain this code, for example:

<?phprequire_once 'include/global.conf.php';$content = array(  'page_title' => 'Log In');$page_mode = form_var('page_mode');if ($page_mode == 'login'){  if (!$sess->login(form_var('username'), sha1(form_var('password'))))  {	$content["error_{$sess->error_field}"] = "<div class=\"text-error\">{$sess->error}</div>";	$content['username'] = form_var('username');  }  else	$sess->redir('admin.php');}$page = new Page();$page->set_template('login.tpl');$page->process($content);$page->output();?>

That will log people in and redirect, and if it fails it will show the login form again with the username filled in and the error message.So I'll use codeboxes for all of this, the Page class is over 1000 lines, but this is the current version of my Page class:edit: never mind, apparently the code is too large. if you're interested I can put together a zip package.

[rattlsnak]

message sent...

[justsomeguy]

I've got everything zipped up, this example does actually work but doesn't really do much other than logging people in and sending emails. But you can take a look at the code to see how various things are done. This includes the user table for the database for the login, but there isn't an example to register a user, you would have to add the database record manually.http://manchine.net/steve/files/w3/fwork.zip

[rattlsnak]

Thanks so much, I'll be able to a look after this weekend!