Combining Tables

46 views
Skip to first unread message

Da-Omiete Iboroma

unread,
Oct 10, 2013, 4:30:58 AM10/10/13
to professi...@googlegroups.com
Hello Everybody,
Please I created two tables which are; result_table and courses_table
with the following columns.

result_table has columns; score, semester, session, unit, code, id,
status, grade, grade_point, matric

courses_table has columns; title, code

MY QUESTION

How do I list all the records for a student by combine the title from
the courses_table with the code on the result_table where matric =
‘100202020’ and semester = ‘harmattan’ and session = ‘2012/2013’

include("database.inc");
$db_name = "database_name";
$table_name2 = "result_table";
$table_name3 = "courses_table";
$connection = mysql_connect("$root", "$user", "$password") or
die(mysql_error());
$db = mysql_select_db($db_name, $connection) or die("couldn't select.");

$sql2 = "
SELECT *
FROM $table_name2
WHERE matric = '$_GET[matric]' AND semester =
'Harmattan' AND session = '$_COOKIE[session]'
ORDER BY code
";
$result = mysql_query($sql2,$connection) or die("couldn't connect.");
$i = 1;
$sumunit = 0;
$score = 0;

while ($row = mysql_fetch_array($result))
{
$serial_number = $i;
$id = $row['id'];
$_POST["code_display"] = $row['code'];
$_POST["unit_display"] = $row['unit'];
$_POST["status_display"] = $row['status'];
$_POST["grade_display"] = $row['grade'];
$_POST["gradepoint_display"] = $row['grade_point'];
$score = $row['score'];
$sumunit = $sumunit + $_POST["unit_display"];
$score = $score + $_POST["score_display"];




$sql4 = "
SELECT *
FROM $table_name3, $table_name2
WHERE $table_name3.code = $table_name2.code AND $table_name2.code =
'$_POST[code_display]'
";
$result = mysql_query($sql4,$connection) or die("couldn't connect.");
while ($row = mysql_fetch_array($result))
{
$_POST["title_use"] = $row['title'];
}

$display_course_block .= "
<tr>
<td width=\"31\" align=\"center\" valign=\"top\">
<p><b>
<font face=\"Verdana\" style=\"font-size: 9pt\">$serial_number</font></b></td>
<td align=\"center\" width=\"102\" valign=\"top\"><p><b>
<font face=\"Verdana\" style=\"font-size:
9pt\">$_POST[code_display]</font></b></td>
<td align=\"left\" width=\"375\" valign=\"top\"><p><b>
<font face=\"Verdana\" style=\"font-size:
9pt\">&nbsp;&nbsp;$_POST[title_use]</font></b></td>
<td align=\"center\" valign=\"top\"><p><b>
<font face=\"Verdana\" style=\"font-size:
9pt\">$_POST[unit_display]</font></b></td>
<td align=\"center\" valign=\"top\"><p><b>
<font face=\"Verdana\" style=\"font-size:
9pt\">$_POST[status_display]</font></b></td>
<td align=\"center\" valign=\"top\"><p><b>
<font face=\"Verdana\" style=\"font-size:
9pt\">$_POST[grade_display]</font></b></td>
<td align=\"center\" width=\"95\" valign=\"top\"><b>
<font face=\"Verdana\" style=\"font-size:
9pt\">$_POST[gradepoint_display]</font></b></td>

</tr>
";
$i++;
@$gpa = ($score/$sumunit);
}

PROBLEM

This displays just one record instead of all the records that matches
the condition.

Please Help!!!!!!!!!!

Damelinks

Robert Gonzalez

unread,
Oct 12, 2013, 1:00:54 PM10/12/13
to professional-php
You're gonna have to ask this a little bit better. How about you run this query on both tables:

SHOW CREATE TABLE <tablename>;

then post the results along with which column from the second table relates to the first table. That will be most helpful.



--
You received this message because you are subscribed to the Google Groups "Professional PHP Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to professional-p...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.



--

Robert Gonzalez
   

Da-Omiete Iboroma

unread,
Oct 15, 2013, 7:05:42 AM10/15/13
to professi...@googlegroups.com, Robert Gonzalez
Thanks for responding. I did what you asked and I got the following

for courses_table

CREATE TABLE `courses_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`title` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


for result_table

CREATE TABLE `result_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`matric` varchar(20) NOT NULL,
`code` varchar(10) NOT NULL,
`session` varchar(20) NOT NULL,
`unit` int(1) NOT NULL,
`status` varchar(1) NOT NULL,
`semester` varchar(10) NOT NULL,
`grade` varchar(1) NOT NULL,
`points` int(2) NOT NULL,
`grade_point` int(2) NOT NULL,
`score` int(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=18259 DEFAULT CHARSET=latin1

The column that relates in both tables is the code column.

My question is how do I list all the titles from the courses_table for
a student by combine the title from the courses_table with the code on
the result_table where matric =
‘100202020’ and semester = ‘harmattan’ and session = ‘2012/2013

Thanks in advance
> <https://plus.google.com/103821781336302925338/about>
> <http://twitter.com/RobertGonzalez>
> <http://www.facebook.com/robertgonzalez>
> <http://www.linkedin.com/in/robertanthonygonzalez>

Da-Omiete Iboroma

unread,
Oct 21, 2013, 3:58:12 PM10/21/13
to Robert Gonzalez, Professional PHP Developers
Thanks alot. It worked excellently.

THANKS THANKS THANKS

Damelinks

On 10/16/13, Robert Gonzalez <robert.anth...@gmail.com> wrote:
> This will get the title from courses whose code matches the code in
> results:
>
> SELECT
> r.id, r.session, r.unit, r.grade, c.title
> FROM
> result_table r JOIN courses_table c ON
> c.code = r.code
>
> That is not entirely what you are asking for, but I suspect you will be
> able to write your own join for that stuff.
>
>
> On Wed, Oct 16, 2013 at 2:33 AM, Da-Omiete Iboroma
> <dame...@gmail.com>wrote:
>
>> Thanks.
>>
>> The whole idea is to produce exams slip for individual student
>> depending on the session and semester.
>>
>> The result_table contains all students results for all semesters and
>> session, while the course_table contains all the course codes and
>> their titles.
>>
>> Working with just the result_table, I am able to retrive all the
>> results for individual semester and session that I desire. I am even
>> able to calculate the GPA and list all the failed courses for that
>> semester and session
>>
>> If you notice closely, the result_table does not contain the titles of
>> the courses. It contains the codes.
>>
>> So my question is how can I retrive the titles of the course codes
>> from the courses_table that matches the conditon.
>>
>> Thanks
>>
>> On 10/15/13, Robert Gonzalez <robert.anth...@gmail.com> wrote:
>> > How are you joining your students table to your courses table? Given
>> > how
>> > the situation looks now, I would not suggest getting all course records
>> for
>> > all students, considering how your tables are architected. But for one
>> > student it might not be too bad.
>> >
>> >
>> > On Tue, Oct 15, 2013 at 4:05 AM, Da-Omiete Iboroma

Da-Omiete Iboroma

unread,
May 15, 2015, 9:14:40 AM5/15/15
to Professional PHP Developers, Robert Gonzalez
Goodday Everybody,

Please I am creating a school management portal for a University.  I am having issues displaying the result on a sheet according to a the students in a particular department and levels.  Please my goal is to make the broadsheet look like the image I attached.

Thanks.

Damelinks






Robert Gonzalez

unread,
May 15, 2015, 2:58:32 PM5/15/15
to Da-Omiete Iboroma, Professional PHP Developers
If you have the data then all you need to do is loop and render.
--

Robert Gonzalez

Ovidiu Alexa

unread,
Jun 7, 2015, 12:09:55 PM6/7/15
to professi...@googlegroups.com, Da-Omiete Iboroma
the problem is that you are rewriting the $row variable: (and $result....)

while ($row = mysql_fetch_array($result))
        { [...]

        $result =  mysql_query($sql4,$connection) or die("couldn't connect.");
        while ($row = mysql_fetch_array($result))
        {
                $_POST["title_use"] = $row['title'];
        }

The solution :) rename the SECOND $row variable to $row_two and the SECOND $result to $result_two. please check all of the variable names

This might be a little too late for you, but :) meh. I'm bored.


Robert Gonzalez

unread,
Nov 28, 2016, 5:53:46 PM11/28/16
to Da-Omiete Iboroma, Professional PHP Developers
Probably because of your domain settings. Check how you are sending your cookie params to make sure they are right for your instance.

On Mon, Nov 28, 2016 at 2:47 PM, Da-Omiete Iboroma <dame...@gmail.com> wrote:
PHP Cookies works well on localhost, but it's not working on live server.  Please why?



--

Robert Gonzalez
Reply all
Reply to author
Forward
0 new messages