SQL select statement/Array help

[bigcat48]

All,

I have a web form that's using php to self submit. I want to use sql statements to pull in data from three different tables to create something that I call a "profile" to be entered into a fourth table called "profile".

I am able to use three tables successfully but when I would try to incorporate the fourth table (contacts) its throwing me for a loop. No pun intended. Well maybe.

I will briefly describe my tables:
table 1: profile <- all data will be submitted here
table 2: company - used for majority of insert information, this table has fields for reps (repIDs) but not contacts
table 3: reps - only used for information for one input field
table 4: contacts - only used for information for one input field

Here is my code.

<?php
$q=$_GET["q"];

include("includes/connection.php");
  
$company_query = "SELECT * FROM `company` WHERE id = '".$q."' ";
$contact_query = "SELECT contact.id FROM `contact` LEFT JOIN `company` on '".$q."' = contact.companyid WHERE company.id = '".$q."' ";

if(!$company_query){ exit('<p>Error in Sql statement!<br />'. 'Error: ' .mysql_error() . '</p>');}
if(!$contact_query){ exit('<p>Error in Sql statement!<br /> Error: .mysql_error()</p>'); }

$rs  = mysql_query($company_query);
$rs2 = mysql_query($contact_query);

while($row = mysql_fetch_array($rs)) {
  // added this section from id to createdBy for the purpose of separating names for ns reps and contact names
  $id = $row['id'];

  $company = htmlspecialchars($row['company_companyName']);
  $city = htmlspecialchars($row['company_city']);
  $state = htmlspecialchars($row['company_state']);

  $nsRepid = htmlspecialchars($row['nsRepid']);
  $nsReps = explode(", ",$nsRepid);
  $nsReplist = "";
	foreach($nsReps as $nsRep){
	  $sql2 = "SELECT * FROM nsreps WHERE id = $nsRep";
	  $results = @mysql_query($sql2);
	  ($nr = mysql_fetch_array($results));
	  $nsReplist .=$nr['nsreps_firstName'] . " ";
	  $nsReplist .=$nr['nsreps_lastName'] . ", ";
	}
  $nsReplist = substr($nsReplist,0,strlen($nsReplist)-2);
  
    [COLOR=red][b]while($row2 = mysql_fetch_array($rs2)) {
      $contacts = $row2["id"];  
      /* //an attempt to load contact first and last names based on contact id in contact table
      $contactIDs = implode(", ",$sql3); // put companyid field data here 
      $contacts = explode(", ",$contactIDs);
      $contactlist = "";
      foreach($contacts as $contact){
        $sql4 = "SELECT id, contact_firstName, contact_lastName FROM contact WHERE id = $contact";
        $resultsCT = @mysql_query($sql4);
        ($ct = mysql_fetch_array($resultsCT));
        $contactlist .=$ct['contact_firstName'] . " ";
        $contactlist .=$ct['contact_lastName'] . ", ";
      }
      $contactlist = substr($contactlist,0,strlen($contactlist)-2);  
	  */
    }
	// seek back to record #1 for the next loop
	mysql_data_seek($rs2, 0);
	$row2 = "";[/b][/color]

  $overview = htmlspecialchars($row['company_overview']);

echo "<div class='row'>
	<label>Company Information:</label>
		<span><input class='disabled w200' type='text' name='company' id='company' value='$company' /><label>Company Name</label></span>
		<span><input class='disabled w180' type='text' disabled='disabled' name='city' id='city' value='$city' /><label>City</label></span>
		<span><input class='disabled w100' type='text' disabled='disabled' name='state' id='state' value='$state' /><label>State</label></span>
</div>";

echo "<div class='row'>
	<label>NS Rep(s):</label>
		<span><input class='disabled text' type='text' name='nsreps' id='nsreps' value='$nsReplist' /><label>NS Reps</label></span>
</div>";

echo "<div class='row'>
	<label>Customer Contact(s):</label>
		<span><input class='disabled text' type='text' name='contacts' id='contacts' value='$contacts' /><label>Customer Contacts</label></span>
</div>";

echo "<div class='row'>
	<label>Overview:</label>
		<textarea class='disabled textarea' type='text' disabled='disabled' name='overview' id='overview'>$overview</textarea>
</div>";
}

mysql_close($dbcnx);
?>

 

[petersJazz]

hi,

havent looked in detail to the code. But the first if statement looks strange, just setting a sql statement in a string can not make it wrong.

I would suggest using a function to load data into an array and after that manipulating it, I find that easier.


$x=qq('SELECT * FROM company WHERE id = "'.$q.'" ');
print_r($x); // view the x array
echo $x[numrows]; // number of rows
echo $x[0]['company']; // view company field in first row


function qq($query,$log=0){
$result=mysql_query($query);
if (!$result){
$tab['numrows']=0;
return $tab;
}
$tab['numrows']=mysql_numrows($result);
while ($row=mysql_fetch_assoc($result)) $tab[]=$row;
return $tab;
}

 

[vacunita]

havent looked in detail to the code. But the first if statement looks strange, just setting a sql statement in a string can not make it wrong.

Perhaps you should try to look into it a bit more. The OP is in fact executing the queries in a mysql_query() function call. It just doesn't happen immediately after setting the variables.

Second I'm not familiar with the qq() function, perhaps you can elaborate on it.



With that out of the way, i'm not entiely sure what you are trying to accomplish with the red code:

  while($row2 = mysql_fetch_array($rs2)) {
      $contacts = $row2["id"]; [green]//This effectively overwrites the $contacts variable each time with a new value. Not sure that's what you want to do, based on the next piece of code [/green] 
      /* //an attempt to load contact first and last names based on contact id in contact table
      $contactIDs = implode(", ",$sql3); // put companyid field data here [green]//$sql3 is never set anywhere in the code you provided. If it is in fact an array, why implode it and then immediately explode it back. Just leave it as is, if you want to  keep the array structure. [/green]
      $contacts = explode(", ",$contactIDs);
      $contactlist = "";
      foreach($contacts as $contact){
        $sql4 = "SELECT id, contact_firstName, contact_lastName FROM contact WHERE id = $contact";
        $resultsCT = @mysql_query($sql4);
        ($ct = mysql_fetch_array($resultsCT));
        $contactlist .=$ct['contact_firstName'] . " ";
        $contactlist .=$ct['contact_lastName'] . ", ";
      }
      $contactlist = substr($contactlist,0,strlen($contactlist)-2);  
      */
    }
    // seek back to record #1 for the next loop
    mysql_data_seek($rs2, 0);
    $row2 = "";

Regardless, I'm not entire sure why you are running separate queries, could you not generate the same information in a single query?

Perhaps you can explain exactly what the output should be from your tables, and we can come up with a single query that can do it instead of looping through query results to generate additional queries.







----------------------------------
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 found a tutorial on w3schools teaching the logic on the php/ajax database concept. Following this tutorial, enabled what I wanted to work.

The issue now is that the second query will only display one entry from my 4th table. Once I select a company from the select drop down menu, the code will only output one value.

I want an output like this:
John Smith, Jane Doe, John Doe (which is essentially id values of 1, 2, 3) being read from the 4th table "contact".

You will notice that in the red text above this reply, I commented out the code that is not functional. I should've deleted it because it is confusing. Sorry.

This is what I should have posted. My explanation including the code.

[COLOR=red]
 while($row2 = mysql_fetch_array($rs2)) {
      $contacts = $row2["id"];  
    }
    // seek back to record #1 for the next loop
    mysql_data_seek($rs2, 0);
    $row2 = "";
[/color]

Overall, what I'm trying to say is that the code that I have is primarily reading one main table called "company" with a field in that table called "repid". Note: On a separate form called "Add a company", I wanted to create a company record also allowing a user to select multiple representatives for that company.

I want to somehow incorporate this same logic, but the field "contactID" is not a field in my table called "company". I don't want this field this table because the purpose of this application is to have each table ("company" & "contact") function separately.

Contact "id" values are based on the company selected.

Meaning you select a company, then you will receive all the contacts associated with that company. The problem is that in my "company" table I don't have a reference for it like I do for "repid".

Sorry for the overkill in the explanation of this post.
Any help would be appreciated. Thanks!

 

[vacunita]

The problem I'm seeing here is that you are using an array of contactID's to generate your queries, that's all fine and good, but I still don't know where $sql3 is coming from.

  $contactIDs = implode(", ",[red]$sql3[/red]); // put companyid field data here
      $contacts = explode(", ",$contactIDs);
      $contactlist = "";
foreach($contacts as $contact){
        $sql4 = "SELECT id, contact_firstName, contact_lastName FROM contact WHERE id = $contact";
        $resultsCT = @mysql_query($sql4);

Judging by your code $sql3 is apparently an array since you are trying to implode it.

But I don't know where its coming from or why it would have a list of contactID's.

Judging by your other variables of similar names such as $sql2 and $sql4 this would be just a string with your query.

However the point here is that you are for all intents and purposes attempting to use the contents of $sql3 to generate your following queries. But I don't know what is in that variable and what would be in your other variables from it.


----------------------------------
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.

 

[petersJazz]

hi,

just trying to help. Sorry if you didnt understand me.

Its no point in doing:

$x='select ...';
if (!$x){exit('<p>Error in Sql statement!<br />'. 'Error: ' .mysql_error() . '</p>');}

you will need to do something with the sql statement before testing for mysql error.