mySQL, cannot get mysql to display my $var

EricOnAdventure · June 15, 2016

Hey guys, here is my page to search mySQL database. It mostly works...but there are some issues.

First $programtype can be set on this or another page. And it works, but when I try

toward to the bottom, it never works. I am not sure why.

Next for whatever reason It seems my sorting only works with overall rank, nothing else, any idea?

Thanks

PS. I will switch to mysqli after i fix this issue.

<?php
session_start();
//error_reporting(0);
include("config.php");

if(isset($_POST["programtype"])){
   $programtype=$_POST["programtype"];
   $_SESSION['programtype']=$programtype;
   }
else {
   $programtype=$_SESSION['programtype'];
   }
   if (!isset($programtype)){
               header("location: index.php");
               exit;
       }

?>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>University Search</title>
<style>
BODY, TD {
   font-family:Arial, Helvetica, sans-serif;
   font-size:12px;
}
</style>
</head>

<body>

<form id="form1" name="form1" method="post" action="search.php">
<label>Country</label>
<select name="Country">
<option value="">--</option>
<?php
   $sql = "SELECT * FROM ".$SETTINGS["data_table"]." GROUP BY Country ORDER BY Country";
   $sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
   while ($row = mysql_fetch_assoc($sql_result)) {
       echo "<option value='".$row["Country"]."'".($row["Country"]==$_REQUEST["Country"] ? " selected" : "").">".$row["Country"]."</option>";
   }
?>

</select>
<label>How do you want to sort the universities?</label>
<select name="sorthow" id="sorthow" >
<option selected="selected" value="">---Select---</option>
<option value="`$programtype`"><?php echo $programtype ?> Ranking</option>
<option value='rank'>Overall Ranking</option>
<option value="`Immigration rate`">Immigration rate</option>
<option value="`Income greater then adverage`">Income</option>
<option value="`Scholerships`">Scholerships</option>
<option value="`TA`">Teaching Asstantships</option>
<option value="`RA`">Research Asstantships</option>
<option value="`satisfaction`">Student Satisfaction</option>
</select>

<input type="submit" name="button" id="button" value="Search" />

</form>
<br /><br />
<table width="700" border="1" cellspacing="0" cellpadding="4">
<tr>
    <td width="191" bgcolor="#CCCCCC"><strong>Odds of Getting Accepted</strong></td>
    <td width="113" bgcolor="#CCCCCC"><strong>Country</strong></td>
    <td width="159" bgcolor="#CCCCCC"><strong><?php echo $programtype ?> Ranking</strong></td>
    <td width="191" bgcolor="#CCCCCC"><strong>Overall Rank</strong></td>
   <td width="191" bgcolor="#CCCCCC"><strong>Income</strong></td>
   <td width="191" bgcolor="#CCCCCC"><strong>Immigration rate</strong></td>
   <td width="191" bgcolor="#CCCCCC"><strong>Scholerships</strong></td>
   <td width="191" bgcolor="#CCCCCC"><strong>Teaching Asstantships</strong></td>
   <td width="191" bgcolor="#CCCCCC"><strong>Research Asstantships</strong></td>
   <td width="191" bgcolor="#CCCCCC"><strong>Student Satisfaction</strong></td>
</tr>
<?php
///country
if ($_REQUEST["Country"]<>'') {
   $search_Country = " AND Country='".mysql_real_escape_string($_REQUEST["Country"])."'";
}

$sorthow=mysql_real_escape_string($_REQUEST["sorthow"]);
//////////////feedback

if ($_REQUEST["sorthow"]<>'') {
   $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE ". " '".mysql_real_escape_string($_REQUEST["sorthow"])."'".$search_country." LIMIT 10";
} else {
   $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE id>0".$search_country." LIMIT 10";
}

///////////////////
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
if (mysql_num_rows($sql_result)>0) {
   while ($row = mysql_fetch_assoc($sql_result)) {
?>
<tr>
    <td>'Members Only'<br> <a href="signup.php">Sign Up now<i class="icon-long-arrow-right"></i></a></td>
    <td><?php echo $row["Country"]; ?></td>
    <td><?php echo $row[$programtype]; ?></td>
    <td><?php echo $row["Rank"]; ?></td>
    <td><?php echo $row["Immigration rate"]; ?></td>
   <td><?php echo $row["Income greater then adverage"]; ?></td>
   <td><?php echo $row["Scholerships"]; ?></td>
   <td><?php echo $row["TA"]; ?></td>
   <td><?php echo $row["RA"]; ?></td>
   <td><?php echo $row["satisfaction"]; ?></td>
</tr>
<?php
   }
} else {
?>
<tr><td colspan="5">Sorry, we didn't find anything, try again.</td>
<?php
}
?>
</table>

</body>
</html>

Muddy_Funster · June 15, 2016

I'm not sure what resource you are using to pick up php bit it looks to be painfully out of date. I read your last post and seen that you were planning to move to mysqli_ after getting the mysql_ code you were using working, but now you have shown that you're using quite a number of mysql_ requests. I'd like to say: please, stop now. You are just giving yourself a load of extra work writing new code that you know you are going to have to rewrite anyway.

That out of the way - what do you mean "it never works"? what's your table structure? do you have a column name in your table that matches the value in $programtype?

EricOnAdventure · June 15, 2016

Hey Muddy,

Not so much SQL here...it seems....but I really want to get it working before moving to mysql as that will also give me a lot of problems.

at current if

it works just fine.

But if

$programtype="Arts";

It fails and tells me it cant find Arts.

No idea what is wrong.

Muddy_Funster · June 15, 2016

for debugging try adding the following in where you are trying to echo out the $row[$programtype]:

if(array_key_exists($programtype, $row)){
echo $row[$programtype];
}
else{
echo $programtype." Was not found as an array key.<br>";
var_dump($programtype);
echo"<br><pre>";
var_dump($row);
echo"</pre>";
}

This should show you the contents and overall string length of $programtype and also the key value pairs of $row array, letting you visually asses if there are any differences. Also note that, as you are working with a php array and not directly with the MySQL table reference, case sensitivity is in effect.

mac_gyver · June 15, 2016

in addition to what has previously been listed, here are some specific things this code needs to do or do differently -

1) your search form should use method='get'. a get request should be used to cause your page to display whatever it is designed to display. a post request should only be used to alter (create, update, delete) data values on the server. the programtype, country, and sorthow values should all be carried in the url. this will eliminate the logic at the top of your code for the programtype. you should however ALWAYS validate that programtype (all input data) is a permitted value before using it.

2) don't use $_REQUEST. use the correct $_GET, $_POST, or $_COOKIE variables where you expect the data to be. for what this code is doing, all inputs should be in and use $_GET.

3) the php code that determines what to do on the page, validates inputs, and forms and run sql queries to retrieve data to display on the page should all be grouped together and come before the start of your html document. this will make it easier to write and test your code and to change the database extension that it uses. this code is commonly called the 'business logic.' the result from the business logic would be php variables. the html document would just use the data that's in the php variables. it would not contain any database extension statements.

4) the sorthow select/option menu should be 'sticky' and select the option that was previously picked.

if the sorthow value is being used to find data, the way you are putting it into the sql statement won't do anything useful. you are putting a literal string value into the WHERE clause WHERE 'a string' ...

if it is being used to produce an ORDER BY ... term, you would need to validate that it only contains a permitted value, you would also need either an ASC or DESC sort direction. then produce the ORDER BY ... part of the sql statement.

Sign In

mySQL, cannot get mysql to display my $var

Recommended Posts

EricOnAdventure

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

EricOnAdventure

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information