joined mysql tables to show customer table with months paid all in one HTML table row

I have a cust table with sid, name, address, phone
I have payments table with sid, month_paid.
I want to show the latest 5 months paid on one line, like this:
 joe guy, 124 chicken place, 5551212, jan, feb, mar, apr, may, <jun>
example of those paid for may, bu not june
SELECT students.sid, students.name, payments.forMonth
  FROM students Inner JOIN payments
 ON students.sid = payments.sid
Right now, this shows a row per each month that was paid.  I would like one row with each month paid shown, and radio buttons for months not paid for 5 months that are based on the current month.  If the radio button is clicked, that month is inserted into payments under that sid.
----
kaileeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Easiest way is to script this
$query="SELECT students.sid, students.name, students.address, payments.forMonth   FROM students Inner JOIN payments  ON  students.sid = payments.sid ";
$result = $mysqli->query($query);
$data = array();
foreach($row = $result->fetch_assoc()) {
   if (!isset($result[$row['sid']]) {
        $data[$row['sid']] = array(
           'sid' => $row['sid'],
           'name' => $row['name'],
           'address' => $row['address'], // Not in your query but you mention it in your sample results
           'payments' => array()
        );
   }
   $data[$row['sid']['payments'][$row['forMonth']] = $row['forMonth'];
   echo '<pre>';
   print_r($data);
   echo '</pre>';
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kaileeAuthor Commented:
foreach($row = $result->fetch_assoc()) {
is generating an error
0
Julian HansenCommented:
@kailee, just a general word of advice. If you want to get the best out of EE you should try to provide as much information as possible so that experts answering your question are best equipped to give you an answer.

A response of "It does not work" or "There was an error" with out any substantiating information just prolongs the process as the experts then have to enquire as to what the error is.

Remember, we cannot see your code or what happens when you run it - anything you can provide to help us understand the problem better would help us help you quicker.

Having said all that - can you tell us exactly what error you are getting.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

kaileeAuthor Commented:
sorry,
Parse error: syntax error, unexpected ')' on that line.
0
kaileeAuthor Commented:
PHP Version 5.4
0
kaileeAuthor Commented:
the line from 'foreach' to 'while' and that cleared the error.
Next error is
Parse error: syntax error, unexpected '{'

Open in new window

from      
if (!isset($result[$row['sid']]) {

Open in new window

0
Julian HansenCommented:
Then just add the missing ')' like so.

if (!isset($result[$row['sid']])) {

Open in new window

0
kaileeAuthor Commented:
Ah, I should've seen that.


Got this error set:
Notice: Undefined variable: mysqli in admin01.php on line 45
Fatal error: Call to a member function query() on a non-object in admin01.php on line 45

Open in new window

line 45 is:
$result = $mysql->query($query);

Open in new window

after a query that has been tested and generates a result.

also, there was a missing bracket at : [$row['forMonth']] in
$data[$row['sid']['payments'][$row['forMonth']]] = $row['forMonth'];

Open in new window

I'm assuming the third one is the correct spot.
0
Julian HansenCommented:
The code posted was a guide - as I don't have access to your code I do not know how you have connected to your database so you will need to modify the code to match what you have.

Here is some working code that demonstrates what you need to be doing
<?php
// Change to your DB connect info

$server = 'localhost';
$user = 'user';
$password = 'pasword';
$database = 'databsae';

// Create the mysqli object

$mysqli = new mysqli($server, $user, $password, $database);

// Check we do not have an erro
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}

// Create the query
$query= <<< QUERY
  SELECT students.sid, students.name, students.address, payments.forMonth
    FROM students Inner JOIN payments  ON  students.sid = payments.sid
QUERY;

// Get the results from the DB
$result = $mysqli->query($query);
$data = array();

while($row = $result->fetch_assoc()) {
   if (!isset($data[$row['sid']])) {
        $data[$row['sid']] = array(
           'sid' => $row['sid'],
           'name' => $row['name'],
           'address' => $row['address'], // Not in your query but you mention it in your sample results
           'payments' => array()
        );
   }
   $data[$row['sid']]['payments'][$row['forMonth']] = $row['forMonth'];
}
echo '<pre>';
print_r($data);
echo '</pre>';

Open in new window

0
kaileeAuthor Commented:
Thank you for your patience.
I have it now dumping data:
    [20] => Array
        (
            [sid] => 20
            [name] => Yota
            [tuition] => 6000
            [payments] => Array
                (
                    [Apr] => Apr
                )

        )

    [21] => Array
        (
            [sid] => 21
            [name] => Haruka
            [tuition] => 8000
            [payments] => Array
                (
                    [Apr] => Apr
                )

        )
    [20] => Array
        (
            [sid] => 20
            [name] => Yota
            [tuition] => 6000
            [payments] => Array
                (
                    [Apr] => Apr
                    [May] => May
                )

        )

    [21] => Array
        (
            [sid] => 21
            [name] => Haruka
            [tuition] => 8000
            [payments] => Array
                (
                    [Apr] => Apr
                )

        )

Open in new window

With this setup, will I be able to make the change, in a table, from this:
		<tr>
			<td id="name">Misao</td>
			<td id="tuition">7000</td>
			<td id="forMonth">May</td>
		</tr>
		<tr>
			<td id="name">Misao</td>
			<td id="tuition">7000</td>
			<td id="forMonth">Apr</td>
		</tr>

Open in new window

To this:
		<tr>
			<td id="name">Misao</td>
			<td id="tuition">7000</td>
			<td id="forMonth">May</td>
			<td id="forMonth">Apr</td>
		</tr>

Open in new window

It looks like the months might have to be assigned to an array named after 'sid'.  Then I would be able to address them when laying out the table, probably within their own loop.
0
Julian HansenCommented:
This is done relatively easily as the months are already in an array - the payments member of each main element is an array of months. The trick is knowing how many columns to create

Two options
1. Limit the number of months you pull from the database per person - this establishes a MAX
2. Loop through the array finding the payments member with the most number of items

In your case you have specified 5 months. The code below is a simple example of how you might do this. Again with out detailed knowledge of your data or your requirements this is just an example, you will need to modify to suit your application.

NOTE In your sample <td> columns above each <td> item in each row has the same id. This is a very bad idea - id's need to be unique. If you need to target those cells for styling use a class. If you have to style individual cells then you have to create a unique id per cell.

Code below
<?php
// Change to your DB connect info

$server = 'localhost';
$user = 'user';
$password = 'password';
$database = 'databsae';

// Create the mysqli object

$mysqli = new mysqli($server, $user, $password, $database);

// Check we do not have an erro
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}

// Create the query
$query= <<< QUERY
  SELECT students.sid, students.name, students.address, payments.forMonth
    FROM students Inner JOIN payments  ON  students.sid = payments.sid
QUERY;

// Get the results from the DB
$result = $mysqli->query($query);
$data = array();

while($row = $result->fetch_assoc()) {
   if (!isset($data[$row['sid']])) {
        $data[$row['sid']] = array(
           'sid' => $row['sid'],
           'name' => $row['name'],
           'address' => $row['address'], // Not in your query but you mention it in your sample results
           'payments' => array()
        );
   }
   if (count($data[$row['sid']]['payments']) < 5) {
     $data[$row['sid']]['payments'][$row['forMonth']] = $row['forMonth'];
   }
}
?>
<table>
  <tr>
    <th>Name</th><th>Address</th><th>Month 1</th><th>Month 2</th><th>Month 3</th><th>Month 4</th><th>Month 5</th>
  </tr>
<?php
foreach($data as $item) {
  echo <<< ROW
    <tr>
      <td>{$item['name']}</td>
      <td>{$item['address']}</td>
ROW;
  $colcount = 0;
  // Populated months
  foreach($item['payments'] as $month) {
    echo <<< COL
      <td>{$month}</td>
COL;
  }    
  // Blank months
  for(;$colcount<5;$colcount++) {
    echo <<< COL
      <td>&nbsp;</td>
COL;
  }
  
  echo <<< ROW
    </tr>
ROW;
}
?>
</table>

Open in new window

0
kaileeAuthor Commented:
Thank you, you've been very helpful.
0
Julian HansenCommented:
You are most welcome - good luck with your project
0
kaileeAuthor Commented:
where can I find out more about lines like
 echo <<< ROW

Open in new window

and
echo <<< COL

Open in new window

I've not seen this style of code.
0
Julian HansenCommented:
That is called Heredoc notation (https://php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc)
It is a means for outputting strings without having to worry about single and double quotes.
0
kaileeAuthor Commented:
Thanks so much!
0
Julian HansenCommented:
You are welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.