Solved

html layout------Php Sql seclect from 2 DB-Tables---------Php Sql Display One row only

Posted on 2014-10-25
13
229 Views
Last Modified: 2014-10-27
Hello Experts

I have 3 questions I need help answering please

Q.1 I would like my table heading going vertical and not horizontal as is the case at the moment I would also like my SQL echo results to display besides the heading and not under them.

Q.2 my php script query FROM w1monsick2 and get the following info
sickteacher2, period2, coverteacher2, subject 2, class2, room2
As you can see in the code there is a date section I need the query to select from a second table called w1monsick and get the date from datepicker

Q.3 With the way my script works at the moment it echo’s the information from the DB-table and displays all the rows. I need the script to only display one row in w1monsick2 ……id2 is my Primary Key….and just in case its needed in w1monsick…….id is my Primary Key

Table Structure of   w1monsick1
Id, period, sickteacher, subject, class, room, coverteacher, datepicker

Table Structure of   w1monsick2
Id2, period2, sickteacher2, subject2, class2, room2, coverteacher2, datepicker2


Thanks Experts for all help given I hope I have explained my self correctly thanks



<?php 
 $con = mysqli_connect("localhost","root","p*s*w*r*","DB-Name"); 
 // Check connection 
 if (mysqli_connect_errno()) 
 { 
 echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
 } 

 $result = mysqli_query($con,"SELECT * FROM w1monsick2"); 

 echo "<table border='1'> 
 <tr> 
 <td>Date</td>
 <td>Sick Teacher</td>
 <td>Period</td> 
 <td>Cover Teacher</td> 
 <td>Subject</td> 
 <td>Class</td> 
 <td>Room</td> 
 </tr>"; 

 while($row = mysqli_fetch_array($result)) 
 { 
 echo "<tr>"; 
 echo "<td>" . $row['datepicker'] . "</td>"; 
 echo "<td>" . $row['sickteacher2'] . "</td>"; 
 echo "<td>" . $row['period2'] . "</td>"; 
 echo "<td>" . $row['coverteacher2'] . "</td>"; 
 echo "<td>" . $row['subject2'] . "</td>"; 
 echo "<td>" . $row['class2'] . "</td>"; 
 echo "<td>" . $row['room2'] . "</td>"; 
 echo "</tr>"; 
 } 
 echo "</table>"; 

 mysqli_close($con); 
 ?> 

Open in new window

0
Comment
Question by:paddy086
  • 7
  • 3
  • 3
13 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 40404756
You might want to step back from this specific set of application development questions and take a little while to get a foundation in how PHP and MYSQL work.  These learning resources can help you with the basics.  Get the most recently published versions.
http://www.sitepoint.com/books/phpmysql5/
http://www.amazon.com/PHP-MySQL-Web-Development-4th/dp/0672329166/

For question #3, you want to use a WHERE clause in the MySQL query.
http://www.w3schools.com/php/php_mysql_where.asp

I do not understand question #2.  The general design of the "datepicker" is something that includes a date in an HTTP request, usually created inside an HTML form and submitted to an action script.  So the date that gets picked would come to the action script in $_GET or $_POST.  The sample code shows that you're getting the datepicker in the rows of the query results set.  This makes me think something is disorganized.  You might want to learn about the HTTP client/server model, which is the request-and-response underpinning of the WWW.  This article might help.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/A_11271-Understanding-Client-Server-Protocols-and-Web-Applications.html

For question #1, the general design pattern is a matrix inversion.  This is not "hard" but can be considered an advanced step in learning computer programming; it requires an understanding of two-dimensional arrays.  This code sample shows how it is done.  The first part of the script is just setup - to load the test data.  The "moving parts" of the design are these:

Line 92-108 gets the column and row titles
Line 112 is the query that will get all of the data we need for the matrices

Line 115-123 creates an empty matrix
Line 126-131 loads the matrix with one configuration
Line 133-162 renders the matrix in tabular form

Line 165-end repeats the process of creating the matrix and displaying the data in an inverted collection

<?php // /demo/matrix_inversion.php
error_reporting(E_ALL);


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/HTML/Q_28544493.html


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATING A TABLE
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id       INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, name     VARCHAR(24) NOT NULL DEFAULT ''
, question VARCHAR(24) NOT NULL DEFAULT ''
, answer   VARCHAR(2)  NOT NULL DEFAULT ''
)
"
;
// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING IT UP WITH TEST DATA
$sql
=
"
INSERT INTO my_table
( name,     question,    answer )
VALUES
( 'David',  'Question1', 'A' ),
( 'John',   'Question1', 'B' ),
( 'David',  'Question2', 'B' ),
( 'Mary',   'Question1', 'C' ),
( 'John',   'Question2', 'A' ),
( 'David',  'Question3', 'B' ),
( 'Mary',   'Question2', 'A' )
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// GET THE COLUMN TITLES (NAMES)
$cols  = array();
$sql   = "SELECT name FROM my_table GROUP BY name ORDER BY name";
$c_res = $mysqli->query($sql);
while ($row = $c_res->fetch_object())
{
    $cols[] = $row->name;
}

// GET THE ROW TITLES (QUESTIONS)
$rows  = array();
$sql   = "SELECT question FROM my_table GROUP BY question ORDER BY question";
$r_res = $mysqli->query($sql);
while ($row = $r_res->fetch_object())
{
    $rows[] = $row->question;
}


// A QUERY THAT CAN BE USED TO LOAD THE MATRIX WITH THE ANSWERS
$sql = "SELECT name, question, answer FROM my_table";


// CREATE THE EMPTY MATRIX OF COLUMNS (NAMES) AND ROWS (QUESTIONS)
$mat = array();
foreach ($cols as $c)
{
    foreach ($rows as $r)
    {
        $mat[$c][$r] = '-';
    }
}


// CREATE THE MATRIX WITH COLUMNS == NAMES AND ROWS == QUESTIONS
$res = $mysqli->query($sql);
while ($row = $res->fetch_object())
{
    $mat[$row->name][$row->question] = $row->answer;
}

// CREATE THE HTML OUTPUT TABLE
$out = '<table>' . PHP_EOL;

// ADD THE COLUMN TITLES
$out .= '<tr>';

// FIRST COLUMN TITLE IS EMPTY
$out .= '<th></th>';
foreach ($cols as $c)
{
    $out .= '<th>' . $c . '</th>';
}
$out .= '</tr>';
$out .= PHP_EOL;

// ADD EACH ROW
foreach ($rows as $r)
{
    $out .= "<tr><td>$r</td>";
    foreach ($cols as $c)
    {
        $out .= '<td>' . $mat[$c][$r] . '</td>';
    }
    $out .= '</tr>';
    $out .= PHP_EOL;
}

// FINISH THE TABLE AND WRITE IT TO THE BROWSER
$out .= '</table>';
echo $out;


// CREATE THE EMPTY MATRIX OF COLUMNS (QUESTIONS) AND ROWS (NAMES)
$mat = array();
foreach ($rows as $r)
{
    foreach ($cols as $c)
    {
        $mat[$r][$c] = '-';
    }
}


// CREATE THE MATRIX WITH COLUMNS == QUESTIONS AND ROWS == NAMES
$res = $mysqli->query($sql);
while ($row = $res->fetch_object())
{
    $mat[$row->question][$row->name] = $row->answer;
}

// ACTIVATE THIS TO SEE THE MATRIX
// print_r($mat);


// CREATE THE HTML OUTPUT TABLE
$out = '<table>' . PHP_EOL;

// ADD THE COLUMN TITLES
$out .= '<tr>';

// FIRST COLUMN TITLE IS EMPTY
$out .= '<th></th>';
foreach ($rows as $r)
{
    $out .= '<th>' . $r . '</th>';
}
$out .= '</tr>';
$out .= PHP_EOL;

// ADD EACH ROW
foreach ($cols as $c)
{
    $out .= "<tr><td>$c</td>";
    foreach ($rows as $r)
    {
        $out .= '<td>' . $mat[$r][$c] . '</td>';
    }
    $out .= '</tr>';
    $out .= PHP_EOL;
}

// FINISH THE TABLE AND WRITE IT TO THE BROWSER
$out .= '</table>';
echo $out;

Open in new window

HTH, ~Ray
0
 

Author Comment

by:paddy086
ID: 40404832
Thank Ray for the WHERE clause  I just could not remember what to do in this point.

As for my question 2 my script looks at a database table called w1monsick2 and gets information from the following
Id2, period2, sickteacher2, subject2, class2, room2, coverteacher2,

What I need is the script to also select from a second table called w1monsick1 but only select the date from this table.
The Date Row in w1monsickl1 is called datepicker..... I will also need to put in a WHERE clause  to only select from the id=1

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>


<?php 
 $con = mysqli_connect("localhost","root","password","DB-Name"); 
 // Check connection 
 if (mysqli_connect_errno()) 
 { 
 echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
 } 

 $result = mysqli_query($con,"SELECT * FROM w1monsick2 WHERE id2 =1"); 

 echo "<table border='1'> 
 <tr> 
 <td>Date</td>
 <td>Sick Teacher</td>
 <td>Period</td> 
 <td>Cover Teacher</td> 
 <td>Subject</td> 
 <td>Class</td> 
 <td>Room</td> 
 </tr>";

 while($row = mysqli_fetch_array($result)) 
 { 
 echo "<tr>";
 echo "<td>" . $row['datepicker'] . "</td>";  
 echo "<td>" . $row['sickteacher2'] . "</td>"; 
 echo "<td>" . $row['period2'] . "</td>"; 
 echo "<td>" . $row['coverteacher2'] . "</td>"; 
 echo "<td>" . $row['subject2'] . "</td>"; 
 echo "<td>" . $row['class2'] . "</td>"; 
 echo "<td>" . $row['room2'] . "</td>"; 
 echo "</tr>"; 
 } 
 echo "</table>"; 

 mysqli_close($con); 
 ?> 
</body>
</html>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40404849
The best way to ask this question is to give us the tools we need to set up an equivalent test bed so we can show you some ways to get the data back from the database.  To that end, the useful information would be something like the lines 30-90 of the matrix inversion script posted above.  We really don't need to see your code -- just show us your test data and give us an idea of what you want to get out of it.  To that end, we would need:

1. The CREATE TABLE statements for the table(s) that are in play.
2. The INSERT statements that will load the test data set into the tables.  

Once we have those things we can work quickly and test each step - in other words, we will be able to give you tested and working code examples that solve the problem.  For my own work, I usually set up the test environment first, before I ever write a line of PHP code.  In my experience, this leads to much faster and more accurate results.  A related concept is the SSCCE.  These things help crystallize our thinking about the problem and help define the "parameters of success" that we want to create in PHP code.
0
 
LVL 33

Assisted Solution

by:Slick812
Slick812 earned 250 total points
ID: 40405188
greetings paddy086, I read your statements and I believe I have a small understanding of what you are trying to have as output, You say ONLY one teacher needs to be displayed in a column-row stack, instead of the traditional row-column stack. I will try and introduce you to this as One Step at a time. I will NOT do the second Table lookup in this first one and just use the incorrect date from datepicker2, that will come after the proper column-row stack for <table> arrangement is done. I often do development ONE step at a time, and not try to do the next output requirement, until I get the first step finished.

Below is some code you will need to look over and see if you understand what it is doing with the <tr> and <td> to get the horizontal view, instead of the vertical view.
<?php 
 $con = mysqli_connect("localhost","root","p*s*w*r*","DB-Name"); 
 // Check connection 
 if (mysqli_connect_errno()) 
 { 
 echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
 } 
// SELECT Limiter ID as $findID
 $findID = 1;
 $result = mysqli_query($con,"SELECT * FROM w1monsick2 WHERE Id2 = $findID");
// if the  $findID is in the Table, this should have only one row output
 
// you need seven ROW strings to collect the 7 different $row[ ] 
$tr1= '<tr><td>Date</td>';
$tr2= '<tr><td>Sick Teacher</td>';
$tr3= '<tr><td>Period</td>';
$tr4= '<tr><td>Cover Teacher</td>';
$tr5= '<tr><td>Subject</td>';
$tr6= '<tr><td>Class</td>';
$tr7= '<tr><td>Room</td>';

 echo "<table border='1'>
 "; 

 while($row = mysqli_fetch_array($result)) 
 { 
 $tr1 .= "<td>".$row['datepicker2'] . "</td>"; 
 $tr2 .="<td>" . $row['sickteacher2'] . "</td>"; 
 $tr3 .="<td>" . $row['period2'] . "</td>"; 
 $tr4 .= "<td>" . $row['coverteacher2'] . "</td>"; 
 $tr5 .= "<td>" . $row['subject2'] . "</td>"; 
 $tr6 .= "<td>" . $row['class2'] . "</td>"; 
 $tr7 .= "<td>" . $row['room2'] . "</td>"; 
 } 
// stop the html row output with a </tr> for each row
 $tr1= '</tr>';
$tr2= '</tr>';
$tr3= '</tr>';
$tr4= '</tr>';
$tr5= '</tr>';
$tr6= '</tr>';
$tr7= '</tr>';

// NOW output with echo all 7 ROW strings
 echo $tr1,'
 ',$tr2,'
 ',$tr3,'
 ',$tr4,'
 ',$tr5,'
 ',$tr6,'
 ',$tr2,'
 </table>'; 

 mysqli_close($con); 
 ?> 

Open in new window

see if this does anything for output that you want on your page.
0
 

Author Comment

by:paddy086
ID: 40405310
Hi Slick812
Thanks for Joining the Question

I have tried to use your script but it only shows a small box in the corner. I have also edited it to see if I could fix it but I made it worse.

So here is my w1monsick1 table in it you can see all the rows w1monsick1 is the only table that contains a DATE row
and its called datepicker
w1monsick1
 
 

Here is w1monsick2 table it contain all the same rows as w1monsick1 but at the end of each name a number 2 is added and there is no DATE row.
w1monsick2
 

I was trying to get all the to look like this
Table
0
 

Author Comment

by:paddy086
ID: 40405325
This works kind of it gets the date from w1monsick1 and it gets all the other info from w1monsick2

the issue is one the layout is wrong
and
issue two  it gets all the rows from w1monsick2 not just one as the WHERE w1monsick2.id2=1 should only get row 1 from this table. it also seems to repeat in getting row 1 eight time for some reason

<?php 
 $con = mysqli_connect("localhost","root","password","test"); 
 // Check connection 
 if (mysqli_connect_errno()) 
 { 
 echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
 } 

$result = mysqli_query($con,"SELECT * FROM w1monsick2,w1monsick1 WHERE w1monsick2.id2=1 or w1monsick1.id =1"); 

 echo "<table border='1'> 
 <tr> 
 <td>Date</td>
 <td>Sick Teacher</td>
 <td>Period</td> 
 <td>Cover Teacher</td> 
 <td>Subject</td> 
 <td>Class</td> 
 <td>Room</td> 
 </tr>"; 

 while($row = mysqli_fetch_array($result)) 
 { 
 echo "<tr>"; 
 echo "<td>" . $row['datepicker'] . "</td>"; 
 echo "<td>" . $row['sickteacher2'] . "</td>"; 
 echo "<td>" . $row['period2'] . "</td>"; 
 echo "<td>" . $row['coverteacher2'] . "</td>"; 
 echo "<td>" . $row['subject2'] . "</td>"; 
 echo "<td>" . $row['class2'] . "</td>"; 
 echo "<td>" . $row['room2'] . "</td>"; 
 echo "</tr>"; 
 } 
 echo "</table>"; 

 mysqli_close($con); 
 ?> 

Open in new window


tablelayout
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40405394
You might want to consider using the GROUP BY clause.  
http://www.tizag.com/mysqlTutorial/mysqlgroupby.php

Sure hope you will consider posting your CREATE TABLE statements and your test data.  We can't copy and install a screen capture image; it just makes a lot of work for somebody else.  It's "nice to see" but doesn't really give you the advantage of having your problem definition and test data sets in the hands of the experts.
0
 

Author Comment

by:paddy086
ID: 40405454
Hi Ray attached is my Sql test Database

test.sql
0
 
LVL 33

Expert Comment

by:Slick812
ID: 40405484
sorry about the display, now that I look at it, I see my mistake as this -

 $tr1= '</tr>';
$tr2= '</tr>';
$tr3= '</tr>';
$tr4= '</tr>';
$tr5= '</tr>';
$tr6= '</tr>';
$tr7= '</tr>';

should have added to the string instead of replace it as -
 $tr1 .= '</tr>';
$tr2 .= '</tr>';
$tr3 .= '</tr>';
$tr4 .= '</tr>';
$tr5 .= '</tr>';
$tr6 .= '</tr>';
$tr7 .= '</tr>';

anyway, you seem to be trying to do this with some attempts in a two table SELECT, but the SQL is not relating to the tables correctly. Tomorrow I will look at this, need sleep now.
0
 

Author Comment

by:paddy086
ID: 40405492
This Code works now the only issue is it is displaying all rows not limiting to one row with line 9 of code
<?php 
 $con = mysqli_connect("localhost","root","PASSWORD","test"); 
 // Check connection 
 if (mysqli_connect_errno()) 
 { 
 echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
 } 

$result = mysqli_query($con,"SELECT * FROM w1monsick2,w1monsick1 WHERE w1monsick2.id2=1 or w1monsick1.id =1"); 

 
$tr1= '<tr><td>Date</td>';
$tr2= '<tr><td>Sick Teacher</td>';
$tr3= '<tr><td>Period</td>';
$tr4= '<tr><td>Cover Teacher</td>';
$tr5= '<tr><td>Subject</td>';
$tr6= '<tr><td>Class</td>';
$tr7= '<tr><td>Room</td>';
  echo "<table border='1'>
 "; 

 while($row = mysqli_fetch_array($result)) 
 { 
 
 $tr1 .="<td>" . $row['datepicker'] . "</td>"; 
 $tr2 .="<td>" . $row['sickteacher2'] . "</td>"; 
 $tr3 .="<td>" . $row['period2'] . "</td>"; 
 $tr4 .= "<td>" . $row['coverteacher2'] . "</td>"; 
 $tr5 .= "<td>" . $row['subject2'] . "</td>"; 
 $tr6 .= "<td>" . $row['class2'] . "</td>"; 
 $tr7 .= "<td>" . $row['room2'] . "</td>"; 
 } 
 $tr1 .= '</tr>';
 $tr2 .= '</tr>';
 $tr3 .= '</tr>';
 $tr4 .= '</tr>';
 $tr5 .= '</tr>';
 $tr6 .= '</tr>';
 $tr7 .= '</tr>';

 echo 
 $tr1,'
 ',$tr2,'
 ',$tr3,'
 ',$tr4,'
 ',$tr5,'
 ',$tr6,'
  ',$tr7,'
 </table>';  

 mysqli_close($con); 
 ?>

Open in new window

0
 

Author Comment

by:paddy086
ID: 40405507
Working now here is what I did with line 9

$result = mysqli_query($con,"SELECT w1monsick2.*, w1monsick1.* FROM w1monsick2, w1monsick1 WHERE w1monsick2.id2 =1 = w1monsick1.id =1");
0
 

Author Closing Comment

by:paddy086
ID: 40405508
Thanks for the help much appreciated
0
 
LVL 33

Expert Comment

by:Slick812
ID: 40406510
Glad you got it to work!, in my way of dealing with more than one table in the SQL text query language, I have found the "INNER JOIN" to be very helpful for this kind of SELECT where one column of one table, MUST equal one column of another table,

tutorials for inner join -
http://www.w3schools.com/sql/sql_join_inner.asp
http://mysqljoin.com/joins/inner-join-tutorial/
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article describes how to create custom column layout styles for Bootstrap. The article uses 5 columns to illustrate the concept, but the principle can be extended to any number of columns.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now