Multiple select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple select

Author

Topic

willo009
Starting Member

5 Posts

Posted - 2013-12-04 : 13:54:12

Hi guys

I have been trying to get my code up and running but i just cannot get it to work. Tried many things, and im new to SQL. I have 3 tables and i have a search form with multiple search fields, that i want to be able to use. So the user can search whatever they want. I will list my tables and there fields below:

Tutors (WHICH HAS)
ID
TutorName
RoomNumber

Modules (WHICH HAS)
ID
Title
ModCode

Semesters (WHICH HAS)
ID
Semester
Year

I have got the Insert working, took me a while to workout as i found we cannot insert into multiple tables at one time in SQL. But i got this part working. My problem now is coding the SELECT statement, so it brings back the search for results. As the information it will be bringing back is from different tables? i have tried using the 'AND' Operator but alas it does not work for me. I could really do with some help on this now as im out of ideas. I will add my SearchPageScript below for you to see. Thankyou for any help with this issue.



<?php
	
	$name = $_POST["Name"];
	$title = $_POST["Title"];
	$code = $_POST['ModCodeSearch'];
	$semester = $_POST['SemesterSearch'];
	$year = $_POST['YearSearch'];
	
		// MAKE CONNECTION TO THE SERVER
	$dbserverip = "localhost";
	$dbusername = "root";
	$dbuserpassword = "jack";
	$connection = mysql_connect($dbserverip, $dbusername, $dbuserpassword) or die ("Could not connect to the SERVER");
	
		// MAKE CONNECTION TO THE DATABASE
	$dbname = "EvilG";
	$dbselectok = mysql_select_db($dbname, $connection) or die ("Could not connect to the DATABASE");
	
	// ISSUE A SELECT COMMAND TO THE RDBMS
	$sqlstatement = "SELECT* FROM Tutors WHERE TutorName LIKE '%$name%'";
	
	echo "<table border=1>";
	
	$sql_result = mysql_query($sqlstatement, $connection)or die ("Could not execute the sql statement");
	
	while ($row = mysql_fetch_array($sql_result))
	{
		echo "<br /><tr bgcolor='#CCCCB2
'><td>Name</td><td>room</td><td>Module</td></tr>";
		echo "<tr><td>".$row['TutorName']."</td><td>".$row['RoomNumber']."<td>".$row['Title']."<td>".$row['ModCode']."</td></tr>";
	}
	echo "</table>";
	
?>

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-04 : 14:26:25

Looks like you want to return these columns, right?
TutorName
RoomNumber
Title
ModCode

Is there any relationship between the tables? ie: are specific tutors associated with specific modules? Looks like your results don't require anything from semesters, right?

Be One with the Optimizer
TG

willo009
Starting Member

5 Posts

Posted - 2013-12-04 : 14:32:36

Hi TG

Yes thats right i want to return the columns you said. And theres a foreign key relationship between Tutors.ID = Module.ID and Module.ID = Semesters.ID. The way i am working it is , i have an Insert_page that inserts into 3 tables, and it gives them all the same value in ID using Auto-Increment.Thanks for your help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-04 : 14:45:54

Normally you should make your ID columns more descriptive like TutorID, ModuleID, etc.

I think you better post some sample data. Because if I understand what you said your Tutors.ID, Module.ID, and Semesters.ID all share the same values? So these tables must all be one-to-one relationships (assuming the foreign keys reference a primary key). Meaning you can't have two tutors for one module. And you can't have two modules in one semester. Doesn't sound like a very good model.

but this is the syntax for joining multiple tables:


select t.TutorName
       ,t.RoomNumber
       ,m.Title
       ,m.ModCode
from   Modules as m
inner join Tutors as t
       on t.ID = m.ID
where  t.tutorName like '%Ralph%'

Be One with the Optimizer
TG

willo009
Starting Member

5 Posts

Posted - 2013-12-04 : 14:57:29

Yes i can see what you mean, so if a tutor had say 3 Modules in in 1 semester it would not work. Ive made up some sample data below, hope this is what you wanted to see.

Semester = 1
Module = Fundamentals of World Domination
ModuleCode = EVX3001
Tutor = Karl Williams
RoomNo = 100
Hours Worked = 5
===================
Semester = 1
Module = Lair Building
ModuleCode = EVX3003
Tutor = Mike Clarke
RoomNo =103
HoursWorked = 6
====================
Semester = 2
Module = Plotting for Beginners
ModuleCode = EVX3005
Tutor = Harris Jones
RoomNo = 133
HoursWorked = 9

Thanks for you insight TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-04 : 15:09:02

>>hope this is what you wanted to see
yes, I think this proves what I was saying. Can you add this data to your tables?
probably not because it looks like you have 2 different tutors and 2 different modules for semester 1.

This model may be better - assuming many-to-many relationships:
- you can have multiple tutors for each module.
- a tutor can be for different modules
- a module can recur in different semesters
- a semester can have multiple modules

Tutors (TutorID, TutorName, RoomNumber)

Modules (ModuleID, Title, ModCode)

Semesters (SemesterID, Semester, Year)

ModuleSemesterAssociation (ModuleID, SemesterID)

TutorModuleAssociation (TotorID, ModuleID)

For a one-to-many:
For instance if a module can ever only have one tutor but a tutor can be in different modules then you could get rid of TutorModuleAssociation and add a tutorID in the Modules table.

Be One with the Optimizer
TG

willo009
Starting Member

5 Posts

Posted - 2013-12-04 : 15:21:55

TG Thankyou for your help, this makes complete sense to me. Im going to implement this right now, start from scratch, add some data and re-test. Once again thanks for taking the time to help me.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-04 : 15:35:25

you're welcome - have fun

Be One with the Optimizer
TG

willo009
Starting Member

5 Posts

Posted - 2013-12-05 : 17:37:42

Hi TG

I'm back haha

The Database works great, i inserted some data and tested in the Shell. But as always, as i'm finding with anything to do with Code once you solve one problem it kicks out another five. The problem i have (didn't know if i should start a new thread?) is i need TutorID in the Tutor_table to also give its value to TutorID in the Modules_table on Insert. I have tried using an extra sql Statement and it works to a point. When i Insert my 1st set of Data its ok, the TutorID updates in the Modules_table. But on 2nd insert of Data within the TutorID in Modules_table it adds 2 rows, and next insert 3 rows etc. I thought maby my sql_result is storing all the other Inserts ID's and added mysql_free_results($sql_result) but didnt work. I will post my Insert.php code so you can see the Insert statements. Thnaks again for any insight.

<?php

echo "<br />Thank you for submitting your form\n";

//EXTRACT THE INFORMATION FROM THE WEB FORM
$name = $_POST['TutorName'];
$roomno = $_POST['RoomNumber'];
$title = $_POST['Title'];
$modcode = $_POST['ModCode'];
$hours = $_POST['Hours'];
$semester = $_POST['Semesters'];
$year = $_POST['Year'];

//MAKE A CONNECTION TO THE DATABASE SERVER
$dbserverip = "localhost";
$dbusername = "root";
$dbuserpassword = "*";
$connection = mysql_connect($dbserverip, $dbusername, $dbuserpassword)or die ("Could not connect to the DBSERVER");

//MAKE A CONNECTION TO THE DATABASE
$dbname = "EvilG2";
$dbselectok = mysql_select_db($dbname, $connection)or die ("Could not connect to the DATABASE");

//ISSUE AN SQL INSERT INTO THE TUTORS TABLE
$sqlstatement ="INSERT INTO Tutors VALUES ('NULL', '$name', '$roomno')";
$sql_result = mysql_query($sqlstatement, $connection)or die ("Could not insert into the DATABASE1");

//ISSUE AND SQL INSERT INTO THE MODULES TABLE
$sqlstatement ="INSERT INTO Modules VALUES ('NULL', 'NULL', '$title', '$modcode', '$hours')";// title, modcode,hours not updating
$sqlstatement ="INSERT INTO Modules (TutorID) SELECT TutorID FROM Tutors ";// works but adds all the other ID`S too
$sql_result = mysql_query($sqlstatement, $connection)or die ("Could not insert into the DATABASE!!2");

//ISSUE AN SQL INSERT INTO THE YEAR TABLE
$sqlstatement ="INSERT INTO Semesters VALUES ('NULL', '$semester', '$year')";
$sql_result = mysql_query($sqlstatement, $connection)or die ("Could not insert into the DATABASE!!3");

echo "Thnakyou Form is Submitted OK!";
?>

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-06 : 10:32:17

quote:

$sqlstatement ="INSERT INTO Modules (TutorID) SELECT TutorID FROM Tutors ";// works but adds all the other ID`S too

This SELECT statement has no WHERE clause so you are returning (and then inserting) all the tutor_ids in the table.

What is typically done is get the new Tutor_id immediately after inserting the new tutor row. Then use that ID as part of your VALUES list for the insert to Modules. Are the IDs identity columns? If so you can use to Scope_identity get the new value.

My preference and a common technique (for a lot of reasons including compartmentalization and security) is to simply call database stored procedures with parameters to handle the database work. That makes for much simpler application code too.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources