Simple INSERT Script Fails - No Error Message

[spock9458]

I am low to medium skilled with MySQL and PHP... I am trying to write a simple script to handle an INSERT command into my database.  The script should be simple, even though there are a lot of data fields.  I have examined my code closely and I think it all looks correct, unless there have been some recent changes in the syntax.  The PHP version on my hosting server is 5.3.27.  Can anyone tell me why this does not insert any data from the html form I'm using?  If you need the form code, I can post that, but I think it is probably something wrong here:

 

<?

//Rob's CompanyDataInsert handler

//DB CONNECTION INFO

mysql_connect("192.168.2.7", "user",
"pwd") or die('Cannot connect to the database because: ' . mysql_error());

mysql_select_db ("nmlta_agents");

//Insert data into 'company' table

$insertData ="INSERT INTO company (id, company, comp_uw, comp_street, comp_po, comp_csz, comp_tf, comp_ph, comp_fx, comp_email, comp_web, comp_county, comp_type, contact1, cont1_title, cont1_email, contact2, cont2_title, cont2_email, contact3, cont3_title, cont3_email, contact4, cont4_title, cont4_email, contact5, cont5_title, cont5_email, contact6, cont6_title, cont6_email, contact7, cont7_title, cont7_email, contact8, cont8_title, cont8_email, contact9, cont9_title, cont9_email, contact10, cont10_title, cont10_email, branch1, br1_street, br1_csz, br1_ph, br1_fx, br1_cont1, br1_cont1_title, br1_cont1_email, br1_cont2, br1_cont2_title, br1_cont2_email, br1_cont3, br1_cont3_title, br1_cont3_email, branch2, br2_street, br2_csz, br2_ph, br2_fx, br2_cont1, br2_cont1_title, br2_cont1_email, br2_cont2, br2_cont2_title, br2_cont2_email, br2_cont3, br2_cont3_title, br2_cont3_email, branch3, br3_street, br3_csz, br3_ph, br3_fx, br3_cont1, br3_cont1_title, br3_cont1_email, br3_cont2, br3_cont2_title, br3_cont2_email, br3_cont3, br3_cont3_title, br3_cont3_email, branch4, br4_street, br4_csz, br4_ph, br4_fx, br4_cont1, br4_cont1_title, br4_cont1_email, br4_cont2, br4_cont2_title, br4_cont2_email, br4_cont3, br4_cont3_title, br4_cont3_email, branch5, br5_street, br5_csz, br5_ph, br5_fx, br5_cont1, br5_cont1_title, br5_cont1_email, br5_cont2, br5_cont2_title, br5_cont2_email, br5_cont3, br5_cont3_title, br5_cont3_email) VALUES ('', '".$_POST['company']."', '".$_POST['comp_uw']."', '".$_POST['comp_street']."', '".$_POST['comp_po']."', '".$_POST['comp_csz']."', '".$_POST['comp_tf']."', '".$_POST['comp_ph']."', '".$_POST['comp_fx']."', '".$_POST['comp_email']."', '".$_POST['comp_web']."', '".$_POST['comp_county']."', '".$_POST['comp_type']."', '".$_POST['contact1']."')", '".$_POST['cont1_title']."')", '".$_POST['cont1_email']."', '".$_POST['contact2']."', '".$_POST['cont2_title']."', '".$_POST['cont2_email']."', '".$_POST['contact3']."', '".$_POST['cont3_title']."', '".$_POST['cont3_email']."', '".$_POST['contact4']."', '".$_POST['cont4_title']."', '".$_POST['cont4_email']."', '".$_POST['contact5']."', '".$_POST['cont5_title']."', '".$_POST['cont5_email']."', '".$_POST['contact6']."', '".$_POST['cont6_title']."', '".$_POST['cont6_email']."', '".$_POST['contact7']."', '".$_POST['cont7_title']."', '".$_POST['cont7_email']."', '".$_POST['contact8']."', '".$_POST['cont8_title']."', '".$_POST['cont8_email']."', '".$_POST['contact9']."', '".$_POST['cont9_title']."', '".$_POST['cont9_email']."', '".$_POST['contact10']."', '".$_POST['cont10_title']."', '".$_POST['cont10_email']."', '".$_POST['branch1']."', '".$_POST['br1_street']."', '".$_POST['br1_csz']."', '".$_POST['br1_ph']."', '".$_POST['br1_fx']."', '".$_POST['br1_cont1']."', '".$_POST['br1_cont1_title']."', '".$_POST['br1_cont1_email']."', '".$_POST['br1_cont2']."', '".$_POST['br1_cont2_title']."', '".$_POST['br1_cont2_email']."', '".$_POST['br1_cont3']."', '".$_POST['br1_cont3_title']."', '".$_POST['br1_cont3_email']."', '".$_POST['branch2']."', '".$_POST['br2_street']."', '".$_POST['br2_csz']."', '".$_POST['br2_ph']."', '".$_POST['br2_fx']."', '".$_POST['br2_cont1']."', '".$_POST['br2_cont1_title']."', '".$_POST['br2_cont1_email']."', '".$_POST['br2_cont2']."', '".$_POST['br2_cont2_title']."', '".$_POST['br2_cont2_email']."', '".$_POST['br2_cont3']."', '".$_POST['br2_cont3_title']."', '".$_POST['br2_cont3_email']."', '".$_POST['branch3']."', '".$_POST['br3_street']."', '".$_POST['br3_csz']."', '".$_POST['br3_ph']."', '".$_POST['br3_fx']."', '".$_POST['br3_cont1']."', '".$_POST['br3_cont1_title']."', '".$_POST['br3_cont1_email']."', '".$_POST['br3_cont2']."', '".$_POST['br3_cont2_title']."', '".$_POST['br3_cont2_email']."', '".$_POST['br3_cont3']."', '".$_POST['br3_cont3_title']."', '".$_POST['br3_cont3_email']."', '".$_POST['branch4']."', '".$_POST['br4_street']."', '".$_POST['br4_csz']."', '".$_POST['br4_ph']."', '".$_POST['br4_fx']."', '".$_POST['br4_cont1']."', '".$_POST['br4_cont1_title']."', '".$_POST['br4_cont1_email']."', '".$_POST['br4_cont2']."', '".$_POST['br4_cont2_title']."', '".$_POST['br4_cont2_email']."', '".$_POST['br4_cont3']."', '".$_POST['br4_cont3_title']."', '".$_POST['br4_cont3_email']."', '".$_POST['branch5']."', '".$_POST['br5_street']."', '".$_POST['br5_csz']."', '".$_POST['br5_ph']."', '".$_POST['br5_fx']."', '".$_POST['br5_cont1']."', '".$_POST['br5_cont1_title']."', '".$_POST['br5_cont1_email']."', '".$_POST['br5_cont2']."', '".$_POST['br5_cont2_title']."', '".$_POST['br5_cont2_email']."', '".$_POST['br5_cont3']."', '".$_POST['br5_cont3_title']."', '".$_POST['br5_cont3_email']."')";

$insert = mysql_query($insertData);
   if(!$insert){
   echo "\nPDO::errorInfo():\n";
   }
   else {
   echo "Database Updated Successfully!";
   }
   
?>
 

The data does not get inserted into my database, and there is absolutely no error message to give me any clue.  Any help will be appreciated - Thanks. Edited August 21, 2013 by Zane

[Psycho]

First off, don't use short tags, i.e. <?

 

Second, I get a parse error on line 14 - which is where you define the query. Why you don't put line breaks in your query to make it readable and provide an error message that would give you a better change of finding the problem is beyond me.

 

Third, your database schema is poorly built. You have multiple "records" of contacts associate with the company. These should be stored in a separate table. Same goes for the "branches" - they belong in a separate table.

 

EDIT: Fourth, the id field should be an auto-increment field - so there's no need to include it in the query at all. In fact, you are specifying a value of an empty string - which is NOT the same as not specifying an ID so the database can assign one. My guess is that the field is an INT type and the insert is not happening because you are setting a value of an empty string for that field - which is not an INT

Edited August 15, 2013 by Psycho

[Psycho]

Fifth: Don't use POST values directly in your query! You should be using PDO or mysqli_ functions, so you can use prepared statements.

 

Sixth: You are running the mysql_ function but then checking for an error using PDO. PDO is not going to report errors if you are not running PDO functions! To see the errors using mysql_ functions, you should use mysql_error();

 

OK, after putting the code into an editor that color codes the content based upon the type (variables, text, functions, etc.) the issue is clear. There are a couple of variables that are not included properly

 

 . . . '".$_POST['contact1']."')", '".$_POST['cont1_title']."')", '".$_POST['cont1_email'].". . .

 

Plus, there are a couple of right parens in the values list. You should only have one at the end of of a record - in this case you have one record, so there should only be one.

 

This should get you going, but doesn't resolve some of the most serious issues (such as using the POST data int he query)

<?php

//Rob's CompanyDataInsert handler

//DB CONNECTION INFO
mysql_connect("192.168.2.7", "user", "pwd")
    or die('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("nmlta_agents");

//Insert data into 'company' table
$sql ="INSERT INTO company
           (company, comp_uw, comp_street, comp_po, comp_csz, comp_tf,
            comp_ph, comp_fx, comp_email, comp_web, comp_county, comp_type,
            contact1, cont1_title, cont1_email, contact2, cont2_title, cont2_email,
            contact3, cont3_title, cont3_email, contact4, cont4_title, cont4_email,
            contact5, cont5_title, cont5_email, contact6, cont6_title, cont6_email,
            contact7, cont7_title, cont7_email, contact8, cont8_title, cont8_email,
            contact9, cont9_title, cont9_email, contact10, cont10_title, cont10_email,
            branch1, br1_street, br1_csz, br1_ph, br1_fx, br1_cont1, br1_cont1_title, br1_cont1_email,
            br1_cont2, br1_cont2_title, br1_cont2_email, br1_cont3, br1_cont3_title, br1_cont3_email,
            branch2, br2_street, br2_csz, br2_ph, br2_fx, br2_cont1, br2_cont1_title, br2_cont1_email,
            br2_cont2, br2_cont2_title, br2_cont2_email, br2_cont3, br2_cont3_title, br2_cont3_email,
            branch3, br3_street, br3_csz, br3_ph, br3_fx, br3_cont1, br3_cont1_title, br3_cont1_email,
            br3_cont2, br3_cont2_title, br3_cont2_email, br3_cont3, br3_cont3_title, br3_cont3_email,
            branch4, br4_street, br4_csz, br4_ph, br4_fx, br4_cont1, br4_cont1_title, br4_cont1_email,
            br4_cont2, br4_cont2_title, br4_cont2_email, br4_cont3, br4_cont3_title, br4_cont3_email,
            branch5, br5_street, br5_csz, br5_ph, br5_fx, br5_cont1, br5_cont1_title, br5_cont1_email,
            br5_cont2, br5_cont2_title, br5_cont2_email, br5_cont3, br5_cont3_title, br5_cont3_email)
       VALUES
           ('{$_POST['company']}', '{$_POST['comp_uw']}', '{$_POST['comp_street']}', '{$_POST['comp_po']}',
            '{$_POST['comp_csz']}', '{$_POST['comp_tf']}', '{$_POST['comp_ph']}', '{$_POST['comp_fx']}',
            '{$_POST['comp_email']}', '{$_POST['comp_web']}', '{$_POST['comp_county']}', '{$_POST['comp_type']}',
            '{$_POST['contact1']}', '{$_POST['cont1_title']}', '{$_POST['cont1_email']}', '{$_POST['contact2']}',
            '{$_POST['cont2_title']}', '{$_POST['cont2_email']}', '{$_POST['contact3']}', '{$_POST['cont3_title']}',
            '{$_POST['cont3_email']}', '{$_POST['contact4']}', '{$_POST['cont4_title']}', '{$_POST['cont4_email']}',
            '{$_POST['contact5']}', '{$_POST['cont5_title']}', '{$_POST['cont5_email']}', '{$_POST['contact6']}',
            '{$_POST['cont6_title']}', '{$_POST['cont6_email']}', '{$_POST['contact7']}', '{$_POST['cont7_title']}',
            '{$_POST['cont7_email']}', '{$_POST['contact8']}', '{$_POST['cont8_title']}', '{$_POST['cont8_email']}',
            '{$_POST['contact9']}', '{$_POST['cont9_title']}', '{$_POST['cont9_email']}', '{$_POST['contact10']}',
            '{$_POST['cont10_title']}', '{$_POST['cont10_email']}', '{$_POST['branch1']}', '{$_POST['br1_street']}',
            '{$_POST['br1_csz']}', '{$_POST['br1_ph']}', '{$_POST['br1_fx']}', '{$_POST['br1_cont1']}',
            '{$_POST['br1_cont1_title']}', '{$_POST['br1_cont1_email']}', '{$_POST['br1_cont2']}',
            '{$_POST['br1_cont2_title']}', '{$_POST['br1_cont2_email']}', '{$_POST['br1_cont3']}',
            '{$_POST['br1_cont3_title']}', '{$_POST['br1_cont3_email']}', '{$_POST['branch2']}',
            '{$_POST['br2_street']}', '{$_POST['br2_csz']}', '{$_POST['br2_ph']}', '{$_POST['br2_fx']}',
            '{$_POST['br2_cont1']}', '{$_POST['br2_cont1_title']}', '{$_POST['br2_cont1_email']}',
            '{$_POST['br2_cont2']}', '{$_POST['br2_cont2_title']}', '{$_POST['br2_cont2_email']}',
            '{$_POST['br2_cont3']}', '{$_POST['br2_cont3_title']}', '{$_POST['br2_cont3_email']}',
            '{$_POST['branch3']}', '{$_POST['br3_street']}', '{$_POST['br3_csz']}', '{$_POST['br3_ph']}',
            '{$_POST['br3_fx']}', '{$_POST['br3_cont1']}', '{$_POST['br3_cont1_title']}', '{$_POST['br3_cont1_email']}',
            '{$_POST['br3_cont2']}', '{$_POST['br3_cont2_title']}', '{$_POST['br3_cont2_email']}',
            '{$_POST['br3_cont3']}', '{$_POST['br3_cont3_title']}', '{$_POST['br3_cont3_email']}',
            '{$_POST['branch4']}', '{$_POST['br4_street']}', '{$_POST['br4_csz']}', '{$_POST['br4_ph']}',
            '{$_POST['br4_fx']}', '{$_POST['br4_cont1']}', '{$_POST['br4_cont1_title']}', '{$_POST['br4_cont1_email']}',
            '{$_POST['br4_cont2']}', '{$_POST['br4_cont2_title']}', '{$_POST['br4_cont2_email']}',
            '{$_POST['br4_cont3']}', '{$_POST['br4_cont3_title']}', '{$_POST['br4_cont3_email']}',
            '{$_POST['branch5']}', '{$_POST['br5_street']}', '{$_POST['br5_csz']}', '{$_POST['br5_ph']}',
            '{$_POST['br5_fx']}', '{$_POST['br5_cont1']}', '{$_POST['br5_cont1_title']}', '{$_POST['br5_cont1_email']}',
            '{$_POST['br5_cont2']}', '{$_POST['br5_cont2_title']}', '{$_POST['br5_cont2_email']}',
            '{$_POST['br5_cont3']}', '{$_POST['br5_cont3_title']}', '{$_POST['br5_cont3_email']}')";

$insert = mysql_query($sql);
if(!$insert)
{
    echo mysql_error();
}
else
{
    echo "Database Updated Successfully!";
}
   
?>

See how much easier that query is to read when there are line breaks and some formatting?

Edited August 15, 2013 by Psycho

[spock9458]

Thank you very much... I am going to try this out right away.  I did download a PHP editor and I think that will also help me.  I know there have been a lot of updates to PHP/MySQL since I did a major project a few years ago, so I'm sure I need to brush up on my methodology.  Once I get this to work, I might try to improve on that, so I may have a few more questions.  I really appreciate your help.

[spock9458]

Just to let you know, your code worked perfectly the first time.  I will spend some time studying the mysqli documentation online to see if I can make it more efficient, but for now I have a working form for data entry in our member directory.  Thanks very much!

[mac_gyver]

i seem to mention arrays in just about every thread i reply in, but you need to use arrays to simplify your code/data. let the computer do the repetitive tasks of listing out everything multiple times, in your form, in your processing/validation code, and in your query. you should only list/define information once, i.e. make your code 'driven' by the data, rather than writing out code for every field and having to find and edit your code any time you add/remove/change anything about the amount of fields. after all, computers were designed to perform repetitive tasks, very fast, tirelessly, and without making mistakes.

 

ignoring any changes your database design needs to normalize it (already mentioned by Psycho), you need to have your list of form/database fields defined in an array (along with the form field type, a legend to display in the form, if they are required or not, what sort of validation to use for each value,...) you would then use this defining array to dynamically produce the form page and use the same array when processing/validating the submitted data and producing the query.

 

the massive INSERT query you have posted would become just a few php statements (after you have validated the data.) one statement that would get all the column names (out of the defining array) and one statement that would get all the data (out of a data array where the validation logic placed it after having validated it.)

[spock9458]

Thanks mac_gyver... I understand the concept of arrays, and would be interested in learning how to better implement them in my code.  Can you point me to an example of what you describe - where the array is defined once, and then used to dynamically produce the form page, etc.?  Thanks

[mac_gyver]

here's a basic example/outline showing how to do this -

// define all the information about the data your code needs
$fields['company'] = array('legend'=>'Company Name','type'=>'text'); // add other elements as needed by the logic
$fields['comp_uw'] = array('legend'=>'I\'m not sure what uw would be','type'=>'text');
$fields['comp_street'] = array('legend'=>'Street Address','type'=>'text');
// define other fields here...


// the following code is 'data' neutral. it doesn't know or care how many pieces of data there are or what type (after the code is completed to handle the different types) they are

// process the form
if($_SERVER['REQUEST_METHOD'] == "POST"){
    $errors = array(); // keep track of validation errors in this array
    $data = array(); // the following logic will place validated, cast/escaped/quoted (as appropriate) data into this array
    // loop over the $fields array and access the corresponding $_POST[$key] to reference the data
    foreach($fields as $key=>$value){

        // your validation logic goes here...

        // for this example code, simply copy/escape/quote string type data
        switch ($value['type']) {
            case "text":
            case "textarea":
                $val = $mysqli->real_escape_string($_POST[$key]); // escape string data, you actually need to use prepared queries to simplify even this
                $data[$key] = "'$val'"; // quote string data
                break;
        }
    }

    if(empty($errors)){
        // validation passed, use the submitted data to produce and run the query
        $query = "INSERT INTO your_table (`".implode('`,`',array_keys($fields))."`) VALUES (".implode(',',$data).")";
    
        echo $query; // see what the query looks like
    }
}

// produce the form

// you would also display any validation error messages in the $errors array

echo "<form method='post' action=''>";
foreach($fields as $key=>$value){
    echo "<label for='$key'>{$value['legend']}: </label>";
    echo "<input type='{$value['type']}' id='$key' name='$key'><br>";
}
echo "<input type='submit'></form>";

 

[spock9458]

I got to thinking - there are a lot of intelligent people who post to these forums, and maybe someone has a better idea for my bigger project that is going on here.  Getting the INSERT query to work is only one small part of the picture.  I am working on an improved online directory for an organization.  The members are companies, who also list the important contact persons at their offices.  A few of the companies also have branch office locations, with different contact people, etc.  It is important that the data retrieved from a member search is formatted in a particular way... my current script works for that, and you can see a small sample of the data in my "working" model here:  http://www.nmlta.org/NewMemDir/bern_member_list.php

 

My database table has to be structured to allow for the largest companies, with the most branches and the most contact people... so right now I have room for 10 main contacts with job title and email addresses, as well as 5 branch companies, each with room for three separate contact people.  My code in order to get the format shown in the sample link above is quite cumbersome, with a lot of row checking to see if there is data in the cell, and if not, it passes to the next line.  It works, but I'm just wondering if there is a better way to do this.  I will attach my php code if someone is interested in looking at it and giving me a better direction.

 

My ultimate goal is even more complicated, as I would like to be able to export the company data to some sort of Word document for using in a printed directory.  With my current format, I don't know how to get to a point where I could use multiple columns in a printed document, since the listing now is quite narrow.  The companies need to be sorted out by County location, but I have figured out how to do that, I'm just kind of stuck on formatting.  I am surprised that I cannot find a few ready-made programs to handle this type of project, but so far I have not.

 

If anyone has some ideas about how to best handle these tasks, I would appreciate any help.  Thanks-

bern_member_list.php

[Barand]

 

My database table has to be structured to allow for the largest companies, with the most branches and the most contact people... so right now I have room for 10 main contacts with job title and email addresses, as well as 5 branch companies, each with room for three separate contact people.  My code in order to get the format shown in the sample link above is quite cumbersome, with a lot of row checking to see if there is data in the cell, and if not, it passes to the next line.  It works, but I'm just wondering if there is a better way to do this.

 

Normalize you data (for the third time). Don't just store spreadsheets in MySQL tables.

[Psycho]

Normalize you data (for the third time). Don't just store spreadsheets in MySQL tables.

 

 

Yep. Based on your description, I would think your structure could look something like this:

 

Table: companies

company_id, company_name, address, phone, fax, web, email

This table should hold all the information specific to the companies. Note, I included a single field for address for brevity, but you may want to split that out into the relevant parts: street, city, state, zip.

 

Table: branches

branch_id, company_id, address, phone, fax, web, email

This table should hold all the information specific to the branches. The company_id field will hold a value that associates each branch to the relevant company..

 

Table: contacts

contact_id, location_type, location_id, contact_name, title, email

This table should hold all the information specific to the contacts. I noticed in your example that some people are associated with the company and others to a branch. SO, there are two fields to take care of this. The location_type would be used to define whether the contact belongs to a company or to a branch. Then the location_id would hold the company_id or the branch_id that the contact should be associated with. Another approach would be to have a column for company_id and another to branch_id and only populate the one that is relevant. This would allow you to associate a contact to both the company and to a branch.

 

The above is only a quick example based upon the information I know. Based upon your actual needs there could be some modifications needed.

Edited August 19, 2013 by Psycho

[spock9458]

Well, now I'm really going to sound ignorant.  That is the initial way I had set up the database, with the exact three tables you have suggested, but I can't wrap my brain around how to pull all of the information in a listing with one query.  I had used (and it does work) four "nested" queries - first selecting from the company table, then selecting from the contact table all matching entries, then selecting from the branch table all matching entries, and then another selection from the contact table any that match the branch.  Is there another way to do a query that will pull everything from all tables at once?  Again I appreciate any advice...  Thanks!

[Barand]

Use SQL JOINS

 

http://forums.phpfreaks.com/page/tutorials/_/data-joins-unions

[spock9458]

Apparently I do not have the necessary permissions to view the tutorial, is there a subscription?  I will look into SQL JOINS, and thanks for that help.

[Barand]

try this link

 

http://www.phpfreaks.com/tutorial/data-joins-unions

[spock9458]

That works - I will definitely check this out!

[Psycho]

Using the example table structure I provided, I think it would make sense to run two queries - one to get the companies and the company contact. Then another to get the branches and branch contact for each company. But you would not run the second query for each company, but would instead run one query for all companies and then associate the data during the processing logic.

 

I would run the two queries and put the results in a multi-dimensional array using company ids and branch ids to get the data structured in a logical format. Then you can easily output the results.

[spock9458]

Thanks Psycho, I can understand what you're saying.  I will work on some queries and see what I can do.  I'll be back if I can't figure it out.

[spock9458]

Have studied the tutorial provided by Barand above... and I'm trying to work through this logically so I can understand, and taking one step at a time.  I have examined the relationships between my data in the company, branch and contact tables.  A company can have many branches, and a branch belongs to only one company (one to many), so I have added the "company_id" field to the contact table.

 

So then my first test is to run a query to SELECT * FROM company WHERE comp_county = 'BERNALILLO' (most of the queries will first want to locate all companies in a particular county, then I added this:) LEFT JOIN contact ON company_id

 

I am trying to run the query in phpmyadmin - should this be OK?  I get a syntax error, and so I've tried: LEFT JOIN contact ON company_id=company_id, and I've also tried USING instead of ON.  I can't get past the syntax errors, and I've really studied the examples.  What am I missing?  Any help will be appreciated, so I can begin to understand the JOIN usage.  Thanks.

[Barand]

When using JOIN ... ON syntax you need to use table names (or table aliases) as prefixes to the column names when specifying the join

... ON company_id = company_id

should be

... ON company.company_id = contact.company_id

[spock9458]

It must be something I'm doing wrong, and I would really like to understand this so I can better myself.  I could not get the query to run in phpmyadmin no matter what I tried.  So I created a "test" php file and uploaded to my server, and when it runs it still gives me a syntax error, which reads as follows:

 

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN contact ON company.company_id = contact.company_id' at line 1"

 

And here is my test code:

 

<?php

// Connection to DB

mysql_connect("192.168.2.7", "user",
"pwd") or die('Cannot connect to the database because: ' . mysql_error());

mysql_select_db ("nmlta_agents");

// Retrieve all the data from the "company" table
$query = mysql_query("SELECT * FROM company WHERE comp_county = 'BERNALILLO' LEFT JOIN contact ON company.company_id = contact.company_id")
or die(mysql_error());  

$result = mysql_query($query);

$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

mysql_close();

If you can help me see why this is not working, I would appreciate it.  Thanks.

?>

[Barand]

JOINs are part of the FROM clause and need to come before the WHERE clause

[spock9458]

OK, making progress... here is my new query:

SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO'

 

This works, even in phpMyAdmin.  The fields from the contact table are appended to the rows in the results, but the values for every cell come back as "NULL" - any idea why that would happen?

 

Thanks

[Barand]

You have a LEFT JOIN so where there is no matching record (on company_id) in the contact table the fields from that table contain null. Check your data

[spock9458]

Yes, that was the problem... I needed to go into the contact table and add the company ids - things are working well now, and I think with all this help I can simplify my code and make things work better.  I will work with it and if I need any more help I will begin a new thread.  Thanks to everyone!