Combining Tables

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\">

$serial_number</td>
<td align=\"center\" width=\"102\" valign=\"top\">
$_POST[code_display]</td>
<td align=\"left\" width=\"375\" valign=\"top\">
  $_POST[title_use]</td>
<td align=\"center\" valign=\"top\">
$_POST[unit_display]</td>
<td align=\"center\" valign=\"top\">
$_POST[status_display]</td>
<td align=\"center\" valign=\"top\">
$_POST[grade_display]</td>
<td align=\"center\" width=\"95\" valign=\"top\">
$_POST[gradepoint_display]</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?