Help with querystring that works with sql statement

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
Nil10
Forum Newbie
Posts: 19
Joined: Thu Jun 30, 2005 1:55 pm

Help with querystring that works with sql statement

Post by Nil10 »

Hi

I have so far inserted this:

WHERE Weekday.WeekdayID='$wdid' && Classroom.ClassroomID='$crid'

And when this is the URL it works like a charm:

*.php?wdid=4&crid=1

But this only selects one of each and discards the rest, what should I do to show more than one or all classrooms or weekdays.
And what if somebody writes just the file in. *.php

Do you have a simpel solution to this?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

you're limiting the number of rows to return in your where clause.

if you want to include more in your results, don't narrow it down to the values in those columns.

if someone just hits yourpage.php w/o any url params, depending on how your server is set up, it will either just die, or you'll get a mysql error, or you wont' see anything at all.

you should check to see of those values are set before you run the query and default them if they're not.
Nil10
Forum Newbie
Posts: 19
Joined: Thu Jun 30, 2005 1:55 pm

Post by Nil10 »

When I run the file alone the page just shows the table header, but it would be nice if it could show all information.

I have to able to both narrow down and choose not to.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

if you want it to show everythign when it's just the page alone, then just default the vars on the page and remove the items from your where clause or just set them to a wildcard.
Nil10
Forum Newbie
Posts: 19
Joined: Thu Jun 30, 2005 1:55 pm

Post by Nil10 »

I don't know about that wildcard thingy can you point it out with some code?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

one way to do it:

Code: Select all

$query = "select * from myTable".(isset($_GET['yourvar']) ? " where yourfield = '".$_GET['yourvar']."'" : "");
another way:

Code: Select all

if(!isset($_GET['yourvar']))
  $where = "%";
else
  $where = $_GET['yourvar'];
$query = "select * from myTable where yourfield = '".$where."'";
Id go with option number one, it'll be cleaner in the long run.
Nil10
Forum Newbie
Posts: 19
Joined: Thu Jun 30, 2005 1:55 pm

Post by Nil10 »

I have tryed the 5 line code and it works the same way as before. The 1 line code I have a hard time to implement cause my sql satement is quite advanced.
Here it is, mind the Danish.

Code: Select all

$query="SELECT Hold.*, Sæsoner.*, Timerfra.*, Minutterfra.*, Timertil.*, Minuttertil.*, Niveauer.*, Stilarter.*, Lokaler.*, Byer.*, Ugedage.*, Undervisere.*
FROM Undervisere INNER JOIN (Ugedage INNER JOIN (Timertil INNER JOIN (Timerfra INNER JOIN (Sæsoner INNER JOIN (Stilarter INNER JOIN (Niveauer RIGHT JOIN (Minuttertil INNER JOIN (Minutterfra INNER JOIN ((Byer INNER JOIN Lokaler ON Byer.ByID = Lokaler.ByID) INNER JOIN Hold ON Lokaler.LokaleID = Hold.LokaleID) ON Minutterfra.MinutterfraID = Hold.MinutterfraID) ON Minuttertil.MinuttertilID = Hold.MinuttertilID) ON Niveauer.NiveauID = Hold.NiveauID) ON Stilarter.StilartID = Hold.StilartID) ON Sæsoner.SæsonID = Hold.SæsonID) ON Timerfra.TimerfraID = Hold.TimerfraID) ON Timertil.TimertilID = Hold.TimertilID) ON Ugedage.UgedagID = Hold.UgedagID) ON Undervisere.UnderviserID = Hold.UnderviserID
WHERE Ugedage.UgedagID='$udid' && Lokaler.LokaleID='$lkid' ORDER BY Lokaler.LokaleID, Ugedage.UgedagID, Timerfra.Timerfra ASC";
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

show me your code to narrow down your search (create the where clause)....
Nil10
Forum Newbie
Posts: 19
Joined: Thu Jun 30, 2005 1:55 pm

Post by Nil10 »

You mean the page my users enter before they reach this databasepage?
I have not made that one yet cause I'd like to first learn how to part the sql before I can do just that.
My thought were to make links that were dynamically created via the sql, and no search option. The database is not that big.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

no I mean the code you're using to narrow down your search options.
Nil10
Forum Newbie
Posts: 19
Joined: Thu Jun 30, 2005 1:55 pm

Post by Nil10 »

This is all the code I use nothing more nothing less

Code: Select all

<?

$username="???";
$password="???";
$database="???";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");

$query="SELECT Hold.*, Sæsoner.*, Timerfra.*, Minutterfra.*, Timertil.*, Minuttertil.*, Niveauer.*, Stilarter.*, Lokaler.*, Byer.*, Ugedage.*, Undervisere.*
FROM Undervisere INNER JOIN (Ugedage INNER JOIN (Timertil INNER JOIN (Timerfra INNER JOIN (Sæsoner INNER JOIN (Stilarter INNER JOIN (Niveauer RIGHT JOIN (Minuttertil INNER JOIN (Minutterfra INNER JOIN ((Byer INNER JOIN Lokaler ON Byer.ByID = Lokaler.ByID) INNER JOIN Hold ON Lokaler.LokaleID = Hold.LokaleID) ON Minutterfra.MinutterfraID = Hold.MinutterfraID) ON Minuttertil.MinuttertilID = Hold.MinuttertilID) ON Niveauer.NiveauID = Hold.NiveauID) ON Stilarter.StilartID = Hold.StilartID) ON Sæsoner.SæsonID = Hold.SæsonID) ON Timerfra.TimerfraID = Hold.TimerfraID) ON Timertil.TimertilID = Hold.TimertilID) ON Ugedage.UgedagID = Hold.UgedagID) ON Undervisere.UnderviserID = Hold.UnderviserID
WHERE Sæsoner.SæsonID='$ssid' && Ugedage.UgedagID='$udid' && Lokaler.LokaleID='$lkid' ORDER BY Lokaler.LokaleID, Ugedage.UgedagID, Timerfra.Timerfra ASC";

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";
?>

<table width="100%" border="1" cellspacing="2" cellpadding="2" class="sortable" id="1">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Nr.</font></th>
<th><font face="Arial, Helvetica, sans-serif">Holdnr</font></th>
<th><font face="Arial, Helvetica, sans-serif">Stilart</font></th>
<th><font face="Arial, Helvetica, sans-serif">niveau</font></th>
<th><font face="Arial, Helvetica, sans-serif">Tid</font></th>
<th><font face="Arial, Helvetica, sans-serif">Lokale</font></th>
<th><font face="Arial, Helvetica, sans-serif">Ugedag</font></th>
<th><font face="Arial, Helvetica, sans-serif">Underviser</font></th>
</tr>

<?
$i=0;
while ($i < $num) {

$holdnr=mysql_result($result,$i,"holdnr");
$timerfra=mysql_result($result,$i,"timerfra");
$minutterfra=mysql_result($result,$i,"minutterfra");
$timertil=mysql_result($result,$i,"timertil");
$minuttertil=mysql_result($result,$i,"minuttertil");
$alderfra=mysql_result($result,$i,"alderfra");
$aldertil=mysql_result($result,$i,"aldertil");
$niveau=mysql_result($result,$i,"niveau");
$niveauid=mysql_result($result,$i,"niveauid");
$stilartnavn=mysql_result($result,$i,"stilartnavn");
$lokale=mysql_result($result,$i,"lokale");
$ugedag=mysql_result($result,$i,"ugedag");
$undervisernavn=mysql_result($result,$i,"undervisernavn");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $i+1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $holdnr; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $stilartnavn; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? if ($niveauid=="0") {echo $alderfra." - ".$aldertil." år";} elseif ($alderfra=="0") {echo $niveau;} else {echo $alderfra." - ".$aldertil." år, ".$niveau;} ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $timerfra.":".$minutterfra." - ".$timertil.":".$minuttertil; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $lokale; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $ugedag; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $undervisernavn; ?></font></td>
</tr>

<?
$i++;
}


echo "</table>";

?>
and I then use this url:
*.php?ssid=1&udid=1&lkid=4_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database&quote;);

$query=&quote;SELECT Hold.*, Sæsoner.*, Timerfra.*, Minutterfra.*, Timertil.*, Minuttertil.*, Niveauer.*, Stilarter.*, Lokaler.*, Byer.*, Ugedage.*, Undervisere.*
FROM Undervisere INNER JOIN (Ugedage INNER JOIN (Timertil INNER JOIN (Timerfra INNER JOIN (Sæsoner INNER JOIN (Stilarter INNER JOIN (Niveauer RIGHT JOIN (Minuttertil INNER JOIN (Minutterfra INNER JOIN ((Byer INNER JOIN Lokaler ON Byer.ByID = Lokaler.ByID) INNER JOIN Hold ON Lokaler.LokaleID = Hold.LokaleID) ON Minutterfra.MinutterfraID = Hold.MinutterfraID) ON Minuttertil.MinuttertilID = Hold.MinuttertilID) ON Niveauer.NiveauID = Hold.NiveauID) ON Stilarter.StilartID = Hold.StilartID) ON Sæsoner.SæsonID = Hold.SæsonID) ON Timerfra.TimerfraID = Hold.TimerfraID) ON Timertil.TimertilID = Hold.TimertilID) ON Ugedage.UgedagID = Hold.UgedagID) ON Undervisere.UnderviserID = Hold.UnderviserID
WHERE Sæsoner.SæsonID='$ssid' && Ugedage.UgedagID='$udid' && Lokaler.LokaleID='$lkid' ORDER BY Lokaler.LokaleID, Ugedage.UgedagID, Timerfra.Timerfra ASC";

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br&gt;&quote;;
?&gt;

&lt;table width=&quote;100%&quote; border=&quote;1&quote; cellspacing=&quote;2&quote; cellpadding=&quote;2&quote; class=&quote;sortable&quote; id=&quote;1&quote;&gt;
&lt;tr&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;Nr.&lt;/font&gt;&lt;/th&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;Holdnr&lt;/font&gt;&lt;/th&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;Stilart&lt;/font&gt;&lt;/th&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;niveau&lt;/font&gt;&lt;/th&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;Tid&lt;/font&gt;&lt;/th&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;Lokale&lt;/font&gt;&lt;/th&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;Ugedag&lt;/font&gt;&lt;/th&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;Underviser&lt;/font&gt;&lt;/th&gt;
&lt;/tr&gt;

&lt;?
$i=0;
while ($i &lt; $num) {

$holdnr=mysql_result($result,$i,&quote;holdnr&quote;);
$timerfra=mysql_result($result,$i,&quote;timerfra&quote;);
$minutterfra=mysql_result($result,$i,&quote;minutterfra&quote;);
$timertil=mysql_result($result,$i,&quote;timertil&quote;);
$minuttertil=mysql_result($result,$i,&quote;minuttertil&quote;);
$alderfra=mysql_result($result,$i,&quote;alderfra&quote;);
$aldertil=mysql_result($result,$i,&quote;aldertil&quote;);
$niveau=mysql_result($result,$i,&quote;niveau&quote;);
$niveauid=mysql_result($result,$i,&quote;niveauid&quote;);
$stilartnavn=mysql_result($result,$i,&quote;stilartnavn&quote;);
$lokale=mysql_result($result,$i,&quote;lokale&quote;);
$ugedag=mysql_result($result,$i,&quote;ugedag&quote;);
$undervisernavn=mysql_result($result,$i,&quote;undervisernavn&quote;);
?&gt;

&lt;tr&gt;
&lt;td&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;&lt;? echo $i+1; ?&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;&lt;? echo $holdnr; ?&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;&lt;? echo $stilartnavn; ?&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;&lt;? if ($niveauid==&quote;0&quote;) {echo $alderfra.&quote; - &quote;.$aldertil.&quote; år&quote;;} elseif ($alderfra==&quote;0&quote;) {echo $niveau;} else {echo $alderfra.&quote; - &quote;.$aldertil.&quote; år, &quote;.$niveau;} ?&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;&lt;? echo $timerfra.&quote;:&quote;.$minutterfra.&quote; - &quote;.$tByer INNER JOIN Lokaler ON Byer.ByID = Lokaler.ByID) INNER JOIN Hold ON Lokaler.LokaleID = Hold.LokaleID) ON Minutterfra.MinutterfraID = Hold.MinutterfraID) ON Minuttertil.MinuttertilID = Hold.MinuttertilID) ON Niveauer.NiveauID = Hold.NiveauID) ON Stilarter.StilartID = Hold.StilartID) ON Sæsoner.SæsonID = Hold.SæsonID) ON Timerfra.TimerfraID = Hold.TimerfraID) ON Timertil.TimertilID = Hold.TimertilID) ON Ugedage.UgedagID = Hold.UgedagID) ON Undervisere.UnderviserID = Hold.UnderviserID
WHERE Sæsoner.SæsonID='$ssid' && Ugedage.UgedagID='$udid' && Lokaler.LokaleID='$lkid' ORDER BY Lokaler.LokaleID, Ugedage.UgedagID, Timerfra.Timerfra ASC";

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";
?>

<table width="100%" border="1" cellspacing="2" cellpadding="2" class="sortable" id="1">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Nr.</font></th>
<th><font face="Arial, Helvetica, sans-serif">Holdnr</font></th>
<th><font face="Arial, Helvetica, sans-serif">Stilart</font></th>
<th><font face="Arial, Helvetica, sans-serif">niveau</font></th>
<th><font face="Arial, Helvetica, sans-serif">Tid</font>&lt;/th&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;Lokale&lt;/font&gt;&lt;/th&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;Ugedag&lt;/font&gt;&lt;/th&gt;
&lt;th&gt;&lt;font face=&quote;Arial, Helvetica, sans-serif&quote;&gt;Underviser&lt;/font&gt;&lt;/th&gt;
&lt;/tr&gt;

&lt;?
$i=0;
while ($i &lt; $num) {

$holdnr=mysql_result($result,$i,&quote;holdnr&quote;);
$timerfra=mysql_result($result,$i,&quote;timerfra&quote;);
$minutterfra=mysql_result($result,$i,&quote;minutterfra&quote;);
$timertil=mysql_result($result,$i,&quote;timertil&quote;);
$minuttertil=mysql_result($result,$i,&quote;minuttertil&quote;);
$alderfra=mysql_result($result,$i,&quote;alderfra&quote;);
$aldertil=mysql_result($result,$i,&quote;aldertil&am<?

$username="???";
$password="???";
$database="???";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");

$query="SELECT Hold.*, Sæsoner.*, Timerfra.*, Minutterfra.*, Timertil.*, Minuttertil.*, Niveauer.*, Stilarter.*, Lokaler.*, Byer.*, Ugedage.*, Undervisere.*
FROM Undervisere INNER JOIN (Ugedage INNER JOIN (Timertil INNER JOIN (Timerfra INNER JOIN (Sæsoner INNER JOIN (Stilarter INNER JOIN (Niveauer RIGHT JOIN (Minuttertil INNER JOIN (Minutterfra INNER JOIN ((Byer INNER JOIN Lokaler ON Byer.ByID = Lokaler.ByID) INNER JOIN Hold ON Lokaler.LokaleID = Hold.LokaleID) ON Minutterfra.MinutterfraID = Hold.MinutterfraID) ON Minuttertil.MinuttertilID = Hold.MinuttertilID) ON Niveauer.NiveauID = Hold.NiveauID) ON Stilarter.StilartID = Hold.StilartID) ON Sæsoner.SæsonID = Hold.SæsonID) ON Timerfra.TimerfraID = Hold.TimerfraID) ON Timertil.TimertilID = Hold.TimertilID) ON Ugedage.UgedagID = Hold.UgedagID) ON Undervisere.UnderviserID = Hold.UnderviserID
WHERE Sæsoner.SæsonID='$ssid' && Ugedage.UgedagID='$udid' && Lokaler.LokaleID='$lkid' ORDER BY Lokaler.LokaleID, Ugedage.UgedagID, Timerfra.Timerfra ASC";

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";
?>

<table width="100%" border="1" cellspacing="2" cellpadding="2" class="sortable" id="1">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Nr.</font></th>
<th><font face="Arial, Helvetica, sans-serif">Holdnr</font></th>
<th><font face="Arial, Helvetica, sans-serif">Stilart</font></th>
<th><font face="Arial, Helvetica, sans-serif">niveau</font></th>
<th><font face="Arial, Helvetica, sans-serif">Tid</font></th>
<th><font face="Arial, Helvetica, sans-serif">Lokale</font></th>
<th><font face="Arial, Helvetica, sans-serif">Ugedag</font></th>
<th><font face="Arial, Helvetica, sans-serif">Underviser</font></th>
</tr>

<?
$i=0;
while ($i < $num) {

$holdnr=mysql_result($result,$i,"holdnr");
$timerfra=mysql_result($result,$i,"timerfra");
$minutterfra=mysql_result($result,$i,"minutterfra");
$timertil=mysql_result($result,$i,"timertil");
$minuttertil=mysql_result($result,$i,"minuttertil");
$alderfra=mysql_result($result,$i,"alderfra");
$aldertil=mysql_result($result,$i,"aldertil");
$niveau=mysql_result($result,$i,"niveau");
$niveauid=mysql_result($result,$i,"niveauid");
$stilartnavn=mysql_result($result,$i,"stilartnavn");
$lokale=mysql_result($result,$i,"lokale");
$ugedag=mysql_result($result,$i,"ugedag");
$undervisernavn=mysql_result($result,$i,"undervisernavn");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $i+1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $holdnr; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $stilartnavn; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? if ($niveauid=="0") {echo $alderfra." - ".$aldertil." år";} elseif ($alderfra=="0") {echo $niveau;} else {echo $alderfra." - ".$aldertil." år, ".$niveau;} ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $timerfra.":".$minutterfra." - ".$timertil.":".$minuttertil; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $lokale; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $ugedag; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $undervisernavn; ?></font></td>
</tr>

<?
$i++;
}


echo "</table>";

?>
and I then use this url:
*.php?ssid=1&udid=1&lkid=4
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

I still don't see anything in there to narrow down your where clause

you need to refine or broaden your search so you're going to have to modify your where clause: look what I posted above.
Nil10
Forum Newbie
Posts: 19
Joined: Thu Jun 30, 2005 1:55 pm

Post by Nil10 »

You mean the 1 line or 5 line code?
Nil10
Forum Newbie
Posts: 19
Joined: Thu Jun 30, 2005 1:55 pm

Post by Nil10 »

Your kidding me is it this easy:

Code: Select all

WHERE ((Lokaler.LokaleID='$lkid' && Sæsoner.SæsonID='$ssid') || (Ugedage.UgedagID='$udid' && Sæsoner.SæsonID='$ssid') || (Stilarter.StilartID='$said' && Sæsoner.SæsonID='$ssid') || (Niveauer.NiveauID='$nvid' && Sæsoner.SæsonID='$ssid') || (Undervisere.UnderviserID='$uvid' && Sæsoner.SæsonID='$ssid'))
but i guess it is not so good regarding security?
Nil10
Forum Newbie
Posts: 19
Joined: Thu Jun 30, 2005 1:55 pm

Post by Nil10 »

I guess Your not writing back, but so much thanks for your time.
Post Reply