CIO Tech Eksperten IT-JOB IT-Kurser Events Podcast Søg

Log ind eller opret profil

Du kan også logge ind via nedenstående tjenester

morphman Nybegynder

18. april 2007 - 22:16 Der er 16 kommentarer og
2 løsninger

Dynamic Search

Hey there lads, I really hope that ye guys will be able to help as I am findin' meself in quite a pickle. The thing is I am creating a really simple school database which consists of tabels such as teacher, student, room and class.

I've been workin' on it for a while and I've gotten must of the functions to work in the backend.

http://mi2oon.monline.dk/3sem/cms/Copy%20of%20search.php

The thing is it sort of looked a little complicated and needed to be expaned. So, I ended up with something that looks like this.

http://mi2oon.monline.dk/3sem/cms/search.php

Pretty simple and neat lookin, huh? Well, at least I think so. I've been woking of the php side of this one so that I actually can check which seach is being made.. and after a little time I came up with this.. and going through all the possible combinations this seems to work well.

However, here is how the php code looks like.

if(isset($_REQUEST['q']) && isset($_REQUEST['class']) && (isset($_REQUEST['student']) || isset($_REQUEST['teacher'])))
{

$name = $_REQUEST['q'];
$student = $_REQUEST['student'];
$teacher = $_REQUEST['teacher'];
$class = $_REQUEST['class'];

$sql_query = "SLECET";

if((strlen($name)>0 || strlen($class)>0)){
if(strlen($name)>0){
echo "Name is set ";
}
if(strlen($class)>0){
echo "Class is set ";
}
if(isset($teacher) && isset($student))
echo "Teacher and Student is set ";
else if(isset($teacher))
echo "Teacher is set ";
else
echo "Student is set ";
}
else{
echo "ERROR: need more input!";
}
}
else {
echo "ERROR: nothing is set!";
}

So, me question is can I make it so that I can create dynamic sql queries meaning queries according to what actually is being set.
I can provide the list of these different queries if what will help.

Well, as I said I really hope that someone will be able to help me with this as I've been stuck of this for a while. Nevertheless, thank ye so much and I am sorry for not writin' danish.. it's just because I am not very good with it. Though I can explain' a little bit if wanted.

thanks and cheers :)

Synes godt om

dandyman Nybegynder

19. april 2007 - 07:35 #1

$sql_query = "SLECET";
should be
$sql_query = "SELECT";

Synes godt om

jakobdo Ekspert

19. april 2007 - 07:56 #2

Could you show the structure of your tables?

Synes godt om

morphman Nybegynder

19. april 2007 - 08:56 #3

dandyman: ahh, it seems like I made a little typo.. thanks for pointin' it out lad ;)

jakobdo: Well, yeah, sure I can.. do ye need the diagram or I can give ye the sql dump? What do ye prefer?

Synes godt om

jakobdo Ekspert

19. april 2007 - 09:18 #4

Just the table-structur, like:

Table_name: student
name - varchar(50)
mail - varchar(255)
etc...

Synes godt om

morphman Nybegynder

19. april 2007 - 11:59 #5

Sure mate here it is :)

CREATE TABLE `class` (
`classid` int(10) unsigned NOT NULL auto_increment,
`roomid` int(2) NOT NULL default '0',
`name` varchar(10) NOT NULL default '',
`line` enum('UK','DK') NOT NULL default 'UK',
`start_year` date NOT NULL default '0000-00-00',
PRIMARY KEY (`classid`),
UNIQUE KEY `name` (`name`)
) TYPE=MyISAM;
CREATE TABLE `postal_code` (
`postal_code` int(4) unsigned NOT NULL default '0',
`district` varchar(30) NOT NULL default '',
PRIMARY KEY (`postal_code`),
UNIQUE KEY `district` (`district`)
) TYPE=MyISAM;
CREATE TABLE `room` (
`roomid` int(2) unsigned NOT NULL auto_increment,
`name` varchar(20) NOT NULL default '',
`area` varchar(20) NOT NULL default '',
PRIMARY KEY (`roomid`),
UNIQUE KEY `name` (`name`)
) TYPE=MyISAM;
CREATE TABLE `student` (
`studentid` int(10) unsigned NOT NULL auto_increment,
`classid` int(10) NOT NULL default '0',
`cpr` int(10) unsigned NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`address` varchar(255) NOT NULL default '',
`postal_code` int(4) unsigned NOT NULL default '0',
`phone` int(15) unsigned NOT NULL default '0',
`email` varchar(255) NOT NULL default '',
PRIMARY KEY (`studentid`),
UNIQUE KEY `cpr` (`cpr`,`email`)
) TYPE=MyISAM;
CREATE TABLE `teacher` (
`teacherid` int(10) unsigned NOT NULL auto_increment,
`classid` int(10) NOT NULL default '0',
`cpr` int(10) unsigned NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`address` varchar(35) NOT NULL default '',
`postal_code` int(4) unsigned NOT NULL default '0',
`phone` int(15) unsigned NOT NULL default '0',
`email` varchar(255) NOT NULL default '',
`subject` enum('int','vis','org','com') NOT NULL default 'int',
PRIMARY KEY (`teacherid`),
UNIQUE KEY `cpr` (`cpr`,`email`)
) TYPE=MyISAM;

Synes godt om

jakobdo Ekspert

19. april 2007 - 12:43 #6

How should the result look like?
If I make a search on NAME and CLASS and check STUDENT, what should i see?
If I make a search on NAME and CLASS and check TEACHER, what should i see?
If I make a search on NAME and CLASS and check STUDENT and TEACHER, what should i see?

Synes godt om

morphman Nybegynder

19. april 2007 - 15:27 #7

Here are all the combinations that I can think of. I am glad that ye are looking into it. thank ye so much mate :)

NAME and CLASS and check STUDENT?
-----------------------------------------------
Find student with that name in that class and
show name address postal_code district phone email
and class.

NAME and CLASS and check TEACHER?
-----------------------------------------------
Find a teacher with that name in that class and
show name address postal_code district phone email
and class.

NAME and CLASS and check STUDENT and TEACHER?
-----------------------------------------------
Find students and teachers with that name in that
class and show name address postal_code district
phone email and class.

NAME and and check STUDENT?
-----------------------------------------------
Find all the students that match that name
show name address postal_code district phone email
and class.

NAME and and check teacher?
-----------------------------------------------
Find all the teachers that match that name
show name address postal_code district phone email
and class.

NAME and check STUDENT and TEACHER?
-----------------------------------------------
Find all the teachers and students that match that name
show name address postal_code district phone email
and class.

CLASS and nothing checked?
-----------------------------------------------
Find the class and show the name, line start_year
and room name

CLASS and check STUDENT?
-----------------------------------------------
Find all the students from that class and show
name address postal_code district phone email
and class.

CLASS and check TEACHER?
-----------------------------------------------
Find all the teachers from that class and show
name address postal_code district phone email
and class.

CLASS and check STUDENT and TEACHER?
-----------------------------------------------
Find all the students and students from that class
and show name address postal_code district phone email
and class.

Synes godt om

morphman Nybegynder

19. april 2007 - 15:35 #8

Well, for me it is like a jungle. I hope that ye can help me lad.
One more thing.. if "CLASS and nothing checked?" is going to cause
some problems.. then just take it out of the equation. ;)

Synes godt om

jakobdo Ekspert

19. april 2007 - 20:35 #9

How do you connect the tables?

Synes godt om

morphman Nybegynder

19. april 2007 - 21:37 #10

Something like this?

http://mi2oon.monline.dk/3sem/cms/datamodel.html

Synes godt om

morphman Nybegynder

19. april 2007 - 21:39 #11

I think is is the old diagram, so a few things have changed.

Students don't have a line but instead got an email
Teachers got a classID so that they can be connected
Class has a name and a line

thanks lad ;)

Synes godt om

jakobdo Ekspert

19. april 2007 - 21:43 #12

How is a teacher connected to the class?
Could there be a classid on the teacher table?

Synes godt om

morphman Nybegynder

19. april 2007 - 21:52 #13

Yes, the diagram only shows the first shot at it. I later found out that I needed to connect teacher to the class.. so yes I added the classID as ye can see in the SQL table structure above. :)

Synes godt om

jakobdo Ekspert

20. april 2007 - 18:32 #14

Just to be sure, you have this line of code:

if(isset($_REQUEST['q']) && isset($_REQUEST['class']) && (isset($_REQUEST['student']) || isset($_REQUEST['teacher'])))

With this code, we have 3 results.
q + class + student
q + class + teacher
q + class + student + teacher

That should be easy to create.

Synes godt om

morphman Nybegynder

04. maj 2007 - 11:02 #15

Well, sorry for the delayed reply. However, I didn't have any internet for a while. Neverhtheless, I got it fixed it's all working now.. thanks for ye help and most of all ye time.. take care till the next time ;)

Synes godt om

morphman Nybegynder

04. maj 2007 - 11:03 #16

ps; if ye want some points, just put down an answer.. cheers ;)

Synes godt om

jakobdo Ekspert

04. maj 2007 - 18:42 #17

Here you got your answer! :o)

Synes godt om

jakobdo Ekspert

20. maj 2007 - 16:55 #18

Thanks mate.

Synes godt om

Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Følg dette spørgsmål

Opret Preview

Webudvikling kurser

Vi tilbyder markedets bedste kurser inden for webudvikling

Se alle Webudvikling kurser

Flere spørgsmål fra PHP kategorien

Titel	Indlæg	Oprettet	Seneste aktivitet
Hent array key Af nemlig i PHP	3	17/05/202415:22	17/05/202416:30
iCal-feed og import til google kalender driller Af nemlig i PHP	11	09/05/202417:49	10/05/202421:28
Adgang til vandaflæsningsdata Af nemlig i PHP	4	22/04/202422:04	30/04/202421:08
Vælg post rækkefølge Af Mojo_dk i PHP	3	06/04/202418:40	07/04/202412:20
Hjælp til PHP Af Friis77 i PHP	5	11/02/202419:58	12/02/202407:55

Se alle spørgsmål i kategorien Opret spørgsmål

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester

Alle kategorier på Eksperten

Seneste artiklerRSS