check two columns to populate form variables

[wkilc]

Hi all,

 

I have a chunk of code that is used to create a pull-down menu that retrieves a list of school names:

<?
//remove any old school from query
$tmp = array();
foreach ($_GET as $fld => $val)
    if (($fld != 'sn_School2')&&($fld != 'start'))
         $tmp[] = $fld . '=' . $val;
$page_name = $_SERVER['SCRIPT_NAME'] . '?' . implode('&',$tmp);
?>
<form name="form2" action="" class="tight">
<?php $result = @mysql_query("select distinct sn_School2 from user_info ORDER BY sn_School2 ASC");
if (mysql_num_rows($result) > 0) {
  print "<select name=\"link\" onchange=\"openURL2()\" style=\"width: 18.0em;\">"; ?>"; ?>
  <option <?php if(empty($_GET['sn_School2'])){ echo "selected=\"selected\""; } ?> value="<? echo "$page_name" ?>">DISPLAY ALL SCHOOLS</option>
  <?php while ($row = mysql_fetch_array($result)) {
if(empty($row['sn_School2'])) continue; //added
    print "<option ";
    if($_GET['sn_School2'] ==   $row['sn_School2']  ){ echo "selected=\"selected\""; }
    print " value=\"$page_name&sn_School2=" . $row['sn_School2'] . "\">" . $row['sn_School2'] . "</option>\n";
  }
  print "</select>";
}
?>
</form>

I didn't write it.... it sorta confuses me, but it works.  It gives me a nice pull down populated by all the school names in School2.  I want to add values from another column, called "School1".  The way the database is set-up is that some folks have a value in "School1", while others have it listed in "School2".  How can I make my pull-down and my query check both 'School1' and 'School2'?  I am thinking I can't, because the query in the URL currently says "www.mysite.com?School2=Hogwarts".

 

I'm a noob... but I think the trick might to create and array, no?

 

Thanks.

Edited July 20, 2016 by wkilc

[benanamen]

Oh where to start...

 

You are using obsolete code that has been completely removed from Php.

Hiding errors with the @ is a no-no.

If you have School1 and School2 in your DB, your database is wrong and needs to be re-worked.

You are directly injecting user supplied data into your code and open for an attack

 

In short, your database and your entire code needs to be re-written.

 

Look up and learn Database Normalization and study this PDO Tutorial https://phpdelusions.net/pdo

 

What you have is junk and dangerous and should not be used whatsoever.

Edited July 20, 2016 by benanamen

[Jacques1]

And why are there two columns for the school? Right now, it sounds like you need to repair your database, not write any code.

 

 

 

I'm a noob...

 

After 11 years?

[wkilc]

Thank you.  The database itself cannot be re-written, I don't think.  Users have TWO potential addresses in their profile.  A primary and a secondary.  EITHER one can be a school/work address.  (Some use the home address as primary, school as secondary, others vice versa.)   I wanted my page to display (and filter using the form) the names of the schools, whether is it listed in the primary OR the secondary address.  If the primary address is a home address, "School1" is empty.  The secondary address is a home address "School2" is empty.  In no case will they both be populated, I do believe.

 

Point taken.  I will hire someone to help with code.  Thanks again.

Edited July 20, 2016 by wkilc

[Jacques1]

It doesn't make sense to hire a programmer when it's a problem with the data. In the worst case, you'll pay a lot of money for useless workarounds.

 

If it's too late to fix the database itself (which is probably the only real solution), you can at least fix the query:

SELECT DISTINCT
    IF(sn_School1 <> '', sn_School1, sn_School2) AS school
FROM
    user_info
ORDER BY
    school ASC

Since I don't know your data, I'm guessing here. Maybe your "empty" actually means NULL rather than an empty string.

[wkilc]

That's very helpful, thank you.  Sadly the database is something I have to download/import a couple times per week from another organization... and it is not practical to change the structure.

[ginerjm]

If you have to import it constantly, it certainly would behoove you to write a translation script to produce a proper db structure!