Page 1 of 1

Help with querystring that works with sql statement

Posted: Thu Jun 30, 2005 3:12 pm
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?

Posted: Thu Jun 30, 2005 3:24 pm
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.

Posted: Thu Jun 30, 2005 4:14 pm
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.

Posted: Thu Jun 30, 2005 4:17 pm
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.

Posted: Thu Jun 30, 2005 4:20 pm
by Nil10
I don't know about that wildcard thingy can you point it out with some code?

Posted: Thu Jun 30, 2005 4:24 pm
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.

Posted: Fri Jul 01, 2005 3:51 am
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";

Posted: Fri Jul 01, 2005 10:45 am
by Burrito
show me your code to narrow down your search (create the where clause)....

Posted: Mon Jul 04, 2005 3:08 am
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.

Posted: Mon Jul 04, 2005 1:24 pm
by Burrito
no I mean the code you're using to narrow down your search options.

Posted: Mon Jul 04, 2005 2:40 pm
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

Posted: Mon Jul 04, 2005 4:44 pm
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.

Posted: Tue Jul 05, 2005 1:08 am
by Nil10
You mean the 1 line or 5 line code?

Posted: Tue Jul 05, 2005 4:55 am
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?

Posted: Thu Jul 07, 2005 1:08 am
by Nil10
I guess Your not writing back, but so much thanks for your time.