Search a database table with a combobox value (solved)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
NEO1976
Forum Newbie
Posts: 7
Joined: Tue Dec 12, 2006 3:00 pm
Location: Nürnberg / Germany

Search a database table with a combobox value (solved)

Post by NEO1976 »

Hallo guys,

I am a bit new (not a novice but also not a big expert) in PHP5 and MySQL.
First of all if there is a similar thread or topic I am sorry for that topic. Unfortunately, I don't have much time to explore through the foren.

What I did is first of all I have created a DB named fernschule
It has three tables:

t_kurse (Courses)
t_institute(Institutes)
t_ins_kurse (for 1 .. * relationship)

t_kurse has these fields:
KURSE_NAME
KURSE_CODE (primary key)

t_institute has these fields:

INS_NAME
INS_CODE (primary key)

t_ins_kurse has two foreign keys which are
INS_CODE
KURSE_CODE

what I am trying to do is to get the all course names sorted by ascending into a combobox and to search if which institutes have these courses.

So my first php file (trial.php) is this

Code: Select all

<?php
/*
 * Created on 12.11.2006
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
 
 $link = mysql_connect('localhost','root','admin');
 $db = mysql_select_db("fernschule");
 if (!$link) {
   die('Could not connect: ' . mysql_error());
}


 
 $sqlquery = "select * from t_kurse ORDER BY KURSE_NAME";
 $res = mysql_query($sqlquery);

 echo "<FORM ACTION = 'trial1.php' METHOD = 'POST'>";
 echo "<SELECT NAME ='t_kurse'>"; 
 while($dsatz = mysql_fetch_assoc($res)) {
  $value = $dsatz["KURSE_CODE"];
  echo "<OPTION VALUE=".$value."SELECTED>".$dsatz["KURSE_NAME"]."</OPTION>";
}
 echo "<INPUT TYPE = 'SUBMIT' VALUE = 'Suchen'>";
 echo "<input type='reset' VALUE = 'Abbrechen'>";
 
mysql_close($link);
?>
I have no problem with it. It has an action to trial1.php. What I wanted to is to get the $value from here via POST method and use it in trial1.php like

Code: Select all

<?php
/*
 * Created on 11.12.2006
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
 include 'trial.php'; 
 $link = mysql_connect('localhost','root','admin');
 $db = mysql_select_db("fernschule");
 if (!$link) {
   die('Could not connect: ' . mysql_error());
}
 $sqlquery = "SELECT t_institute.INS_NAME
FROM t_kurse INNER JOIN (t_institute INNER JOIN t_ins_kurse ON t_institute.INS_CODE=t_ins_kurse.INS_CODE) ON t_kurse.KURSE_CODE=t_ins_kurse.KURSE_CODE
WHERE(((t_kurse.KURSE_CODE)=" .$_POST['value']."))";
 $res = mysql_query($sqlquery);
  while($dsatz = mysql_fetch_assoc($res)) {
  echo "<p>".$dsatz["INS_NAME"]."</P>";
}
?>
But it doesn't work. What am I doing wrong? Can anyone help me please?
Last edited by NEO1976 on Wed Dec 13, 2006 5:21 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Something similar to

Code: Select all

SELECT
  t_institute.INS_NAME
FROM t_institute
INNER JOIN t_ins_kurse
  ON
    t_institute.INS_CODE = t_ins_kurse.INS_CODE
INNER JOIN t_kurse
  ON
    t_ins_kurse.KURSE_CODE = t_kurse.KURSE_CODE
    AND
    t_kurse.KURSE_CODE = 'foo'
Make sure you validate, verify and escape user submitted data that is used in queries.
NEO1976
Forum Newbie
Posts: 7
Joined: Tue Dec 12, 2006 3:00 pm
Location: Nürnberg / Germany

Post by NEO1976 »

Thanks feyd. The fact is the user doesn't need to log in. What I also wanted to if my first php file is right. My aim is to get the $value variable from tiral.php voe HTTP POST and to use it in the trial1.php to get the results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I didn't say anything about a user being logged in. I said user submitted data. That's anything you get from outside of server created sources. $_GET, $_POST, $_REQUEST, $_COOKIE, many pieces of $_SERVER are from external sources and must be validated and verified to keep the security of the application held.
NEO1976
Forum Newbie
Posts: 7
Joined: Tue Dec 12, 2006 3:00 pm
Location: Nürnberg / Germany

Post by NEO1976 »

Thanks feyd. As an ASP and JSP programmer I am a bit new to PHP. You have written

Code: Select all

SELECT 
  t_institute.INS_NAME 
FROM t_institute 
INNER JOIN t_ins_kurse 
  ON 
    t_institute.INS_CODE = t_ins_kurse.INS_CODE 
INNER JOIN t_kurse 
  ON 
    t_ins_kurse.KURSE_CODE = t_kurse.KURSE_CODE 
    AND 
    t_kurse.KURSE_CODE = 'foo'
is this foo gonna be
t_kurse.KURSE_CODE = '$_POST['value']' ? Is the syntax correct?

Thanks a lot.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

"foo" would be replaced by whatever you wish to search for, be it $_POST['something'] or "bar" .. :)
NEO1976
Forum Newbie
Posts: 7
Joined: Tue Dec 12, 2006 3:00 pm
Location: Nürnberg / Germany

Post by NEO1976 »

Thanks feyd. At least I don't meet any error messages now but althought it takes the $value variable in trial.php it doesn`t send it to trial1.php via post. This is my code chunk from trial.php

Code: Select all

$sqlquery = "select * from t_kurse ORDER BY KURSE_NAME";
 $res = mysql_query($sqlquery);

 echo "<FORM ACTION = 'trial1.php' METHOD = 'POST'>";
 echo "<SELECT NAME ='t_kurse'>"; 
 while($dsatz = mysql_fetch_assoc($res)) {
  $value = $dsatz["KURSE_CODE"];
  echo "<OPTION VALUE=".$value.">".$dsatz["KURSE_NAME"]."</OPTION>";
}
 echo "<INPUT TYPE = 'SUBMIT' VALUE = 'Suchen'>";
 echo "<input type='reset' VALUE = 'Abbrechen'>";
 echo "</FORM>";
I have also checked from its view source if it really takes the value variable as OPTION VALUE and it takes but it doesn't send it to trial1.php via POST. I am sure that I am doing a really silly mistake but I cannot see it. S*t that's why I hate to learn a new programming language :D
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The <select> wasn't closed. That may lead to issues.
NEO1976
Forum Newbie
Posts: 7
Joined: Tue Dec 12, 2006 3:00 pm
Location: Nürnberg / Germany

Post by NEO1976 »

Ok I closed it but it doesn't still pass the variable via POST.

trial.php:

Code: Select all

<?php
/*
 * Created on 12.11.2006
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
 
 $link = mysql_connect('localhost','root','admin');
 $db = mysql_select_db("fernschule");
 if (!$link) {
   die('Could not connect: ' . mysql_error());
}


 
 $sqlquery = "select * from t_kurse ORDER BY KURSE_NAME";
 $res = mysql_query($sqlquery);

 echo "<FORM ACTION = 'deneme1.php' METHOD = 'POST'>";
 echo "<SELECT NAME ='t_kurse'>"; 
 while($dsatz = mysql_fetch_assoc($res)) {
  $value = $dsatz["KURSE_CODE"];
  echo "<OPTION VALUE=".$value.">".$dsatz["KURSE_NAME"]."</OPTION>";
}
 echo "</SELECT>"; 
 echo "<INPUT TYPE = 'SUBMIT' VALUE = 'Suchen'>";
 echo "<input type='reset' VALUE = 'Abbrechen'>";
 echo "</FORM>";
 
?>
trial1.php

Code: Select all

<?php
/*
 * Created on 11.12.2006
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
 include 'trial.php'; 
 echo "value is $_POST[value]"; 
 $link = mysql_connect('localhost','root','admin');
 $db = mysql_select_db("fernschule");
 if (!$link) {
   die('Could not connect: ' . mysql_error());
}
 $sqlquery = "SELECT 
  t_institute.INS_NAME 
FROM t_institute 
INNER JOIN t_ins_kurse 
  ON 
    t_institute.INS_CODE = t_ins_kurse.INS_CODE 
INNER JOIN t_kurse 
  ON 
    t_ins_kurse.KURSE_CODE = t_kurse.KURSE_CODE 
    AND 
    t_kurse.KURSE_CODE = '$_POST[value]'";
 $res = mysql_query($sqlquery);
  while($dsatz = mysql_fetch_assoc($res)) {
  echo "<p>".$dsatz["INS_NAME"]."</P>";
}
?>
I am sorry I cannot find the silly mistake. I am feeling myself like a dummy user now :(
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Your code is using $_POST['value'] ... that's not the name of the select tag. ;)
NEO1976
Forum Newbie
Posts: 7
Joined: Tue Dec 12, 2006 3:00 pm
Location: Nürnberg / Germany

Post by NEO1976 »

I thought I should have used the table name as SELECT value. So how should I correct this error? I cannot define the $value variable outside the while loop and I want to send this $value variable via POST in order to use it in my SQL query in trial1.php. Sorry I know I am asking too many questions. :oops:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I gave a pretty big hint in my last post. :)
NEO1976
Forum Newbie
Posts: 7
Joined: Tue Dec 12, 2006 3:00 pm
Location: Nürnberg / Germany

Post by NEO1976 »

That's it! Sorry it is now midnight here I am a bit sleepy. I couldn't get it first what you meant. Ok the problem is solved. I just want to write my php codes down again to show the others the solutions.

trial.php:

Code: Select all

<?php
/*
 * Created on 12.11.2006
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
 
 $link = mysql_connect('localhost','root','admin');
 $db = mysql_select_db("fernschule");
 if (!$link) {
   die('Could not connect: ' . mysql_error());
}


 
 $sqlquery = "select * from t_kurse ORDER BY KURSE_NAME";
 $res = mysql_query($sqlquery);

 echo "<FORM ACTION = 'trial1.php' METHOD = 'POST'>";
 echo "<SELECT NAME ='kurse'>";
 while($dsatz = mysql_fetch_assoc($res)) {
  $value = $dsatz["KURSE_CODE"];
 
  echo "<OPTION VALUE=".$value.">".$dsatz["KURSE_NAME"]."</OPTION>";
  
}
 echo "</SELECT>"; 
 echo "<INPUT TYPE = 'SUBMIT' VALUE = 'Suchen'>";
 echo "<input type='reset' VALUE = 'Abbrechen'>";
 echo "</FORM>";
 
?>
trial1.php

Code: Select all

<?php
/*
 * Created on 11.12.2006
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */

 $link = mysql_connect('localhost','root','admin');
 $db = mysql_select_db("fernschule");
 if (!$link) {
   die('Could not connect: ' . mysql_error());
}
 $sqlquery = "SELECT 
  t_institute.INS_NAME 
FROM t_institute 
INNER JOIN t_ins_kurse 
  ON 
    t_institute.INS_CODE = t_ins_kurse.INS_CODE 
INNER JOIN t_kurse 
  ON 
    t_ins_kurse.KURSE_CODE = t_kurse.KURSE_CODE 
    AND 
    t_kurse.KURSE_CODE = '$_POST[kurse]'";
 $res = mysql_query($sqlquery);
  while($dsatz = mysql_fetch_assoc($res)) {
  echo "<p>".$dsatz["INS_NAME"]."</P>";
}
?>
Post Reply