Display array data 1

[bigcat48]

All:

I have one big mess that I don't know how to describe well but here it goes. I am having trouble displaying array data.

I have a record with the column name "eventid" filled with comma separated values. I need every array value matched with another table that has its name.

*NOTE: An id or record in the contact table can have multiple events.

Example:
tblContact
id = 1, eventid = 1, 2, 3

match with

tblEvents
id = 1, eventName = Spring
id = 2, eventName = Summer
id = 3, eventName = Fall

which means
tblContact
id = 1, eventid = 1(spring), 2(summer), 3(fall)


List page code:

$sql = @mysql_query('SELECT contact.id, company.company_companyName, contact.contact_firstName, contact.contact_lastName, contact.contact_title, contact.contact_response, events.eventName, contact.eventid
FROM `contact` 
left join `company` ON contact.companyid = company.id
left join `events` ON contact.eventid = events.id
ORDER BY contact.id DESC');
if(!sql){
 exit('<p>Error in Sql statement!<br />'.
 'Error: ' .mysql_error() . '</p>');
}

while ($sql1 = mysql_fetch_array($sql)) {
  $id = $sql1['id'];
  $companyName = htmlspecialchars($sql1['company_companyName']);
  $firstName = htmlspecialchars($sql1['contact_firstName']);
  $lastName = htmlspecialchars($sql1['contact_lastName']);
  $title = htmlspecialchars($sql1['contact_title']);
  $eventid = htmlspecialchars($sql1['eventid']);
  $response = htmlspecialchars($sql1['contact_response']);

  echo "<tr>".
	   "<td>$id</td>".
	   "<td>$companyName</td>".
	   "<td>$firstName</td>".
	   "<td>$lastName</td>".
	   "<td>$title</td>".
	   "<td>$eventid</td>".

Could I possibly create another while loop inside of the while loop to display the matching event name?

Thank you for any help.

 

[vacunita]

You could, but if it where me I'd fix the real issue, and that's actually having a field in a table with comma separated values. That is not normalized relational DB design.
And will only cause further headaches down the line.

The easiest would be to have a table that holds the
relationships between the contacts and the events in a 1 to 1 basis.

But getting back to your question:

while ($sql1 = mysql_fetch_array($sql)) {
  $id = $sql1['id'];
  $companyName = htmlspecialchars($sql1['company_companyName']);
  $firstName = htmlspecialchars($sql1['contact_firstName']);
  $lastName = htmlspecialchars($sql1['contact_lastName']);
  $title = htmlspecialchars($sql1['contact_title']);
  $eventid = htmlspecialchars($sql1['eventid']);
  $response = htmlspecialchars($sql1['contact_response']);
[red]
  $events=explode(",",$eventid);
  $eventlist="";
  foreach($events as $event){
   $sql2="SELECT *FROM tblEvents WHERE id=$event";
   $results=mysql_query($sql);
   $ev=mysql_fetch_array($results);
   $eventlist.=$ev['eventname'] . ",";
}

}

[/red]

  echo "<tr>".
       "<td>$id</td>".
       "<td>$companyName</td>".
       "<td>$firstName</td>".
       "<td>$lastName</td>".
       "<td>$title</td>".
       "<td>[red]$eventlist[/red]</td>".

No I wrote this straight into the reply box, so there could be errors I'm not noticing.


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[bigcat48]

Big Thank You!!! Have I ever told you, your awesome?

This worked. But I can't figure out how to remove the extra comma on the end of the result.

Here's the code.

while ($sql1 = mysql_fetch_array($sql)) {
  $id = $sql1['id'];
  $companyName = htmlspecialchars($sql1['company_companyName']);
  $firstName = htmlspecialchars($sql1['contact_firstName']);
  $lastName = htmlspecialchars($sql1['contact_lastName']);
  $title = htmlspecialchars($sql1['contact_title']);
  $eventid = htmlspecialchars($sql1['eventid']);
  
  $events = explode(", ",$eventid);
  $eventlist = "";
	foreach($events as $event){
	  $sql2 = "SELECT * FROM events WHERE id = $event";
	  $results = @mysql_query($sql2);
	  ($ev = mysql_fetch_array($results));
	  $eventlist .=$ev['eventName'] . ", " ;
	}
  $response = htmlspecialchars($sql1['contact_response']);

  echo "<tr>".
	   "<td>$id</td>".
	   "<td>$companyName</td>".
	   "<td>$firstName</td>".
	   "<td>$lastName</td>".
	   "<td>$title</td>".
	   "<td>$eventlist</td>".

 

[vacunita]

The easy way, just delete one character from the final string.

foreach($events as $event){
      $sql2 = "SELECT * FROM events WHERE id = $event";
      $results = @mysql_query($sql2);
      ($ev = mysql_fetch_array($results));
      $eventlist .=$ev['eventName'] . ", " ;
    }

[red]$eventlist=substr($eventlist,0,strlen($eventlist)-1);[/red]

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[bigcat48]

I included:

while ($sql1 = mysql_fetch_array($sql)) {
  $id = $sql1['id'];
  $companyName = htmlspecialchars($sql1['company_companyName']);
  $firstName = htmlspecialchars($sql1['contact_firstName']);
  $lastName = htmlspecialchars($sql1['contact_lastName']);
  $title = htmlspecialchars($sql1['contact_title']);
  $eventid = htmlspecialchars($sql1['eventid']);  
  $events = explode(", ",$eventid);
  $eventlist = "";
	foreach($events as $event){
	  $sql2 = "SELECT * FROM events WHERE id = $event";
	  $results = @mysql_query($sql2);
	  ($ev = mysql_fetch_array($results));
	  $eventlist .=$ev['eventName'] . ", ";
	}
  $eventlist = substr($eventlist,0,strlen($eventlist)-1);
  
  $response = htmlspecialchars($sql1['contact_response']);

  echo "<tr>".
	   "<td>$id</td>".
	   "<td>$companyName</td>".
	   "<td>$firstName</td>".
	   "<td>$lastName</td>".
	   "<td>$title</td>".
	   "<td>$eventlist</td>".

Even though its reading the variable $eventlist that calls for removing the extra comma, its still not removing the extra comma.

Am I doing something wrong?

 

[vacunita]

You ave an additional space there:

$eventlist .=$ev['eventName'] . ",[red]_[/red]";
    }
  $eventlist = substr($eventlist,0,strlen($eventlist)-1);

so instead of 1 character you need to remove 2 to get the comma.

$eventlist = substr($eventlist,0,strlen($eventlist)-[red]2[/red]);

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[bigcat48]

thanks. that worked.

 

[bigcat48]

All:

Code works but don't know why I am receiving this message:

"Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\pcl\list_contacts.php on line 109"

No clue.

  $events = explode(", ",$eventid);
  $eventlist = "";
	foreach($events as $event){
	  $sql2 = "SELECT * FROM events WHERE id = $event";
	  $results = @mysql_query($sql2);
	  ($ev = mysql_fetch_array($results));
	  $eventlist .=$ev['eventName'] . ", ";
	}
  $eventlist = substr($eventlist,0,strlen($eventlist)-2);

 

[bigcat48]

Issue resolved with the error message. Found out that the problem was with the data in the table.

The code could not read the value "Array".

I manually changed the value in the table to the desired value and it removed the error message.

I am getting better. That's cool. Now I am really a cornball.