user6618037 user6618037 - 1 year ago 66
MySQL Question

Why is my JOIN not working?

I have two tables. My first table ps_product which contains id_product, price, unity and reference. I am trying to join on another table called ps_product_lang which contains a column called name.

I am getting the following error:

Error: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id_product' in field list is ambiguous

I have also provided my code below:

try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$stmt = $conn->prepare("SELECT id_product, price, unity, reference FROM ps_product JOIN ps_product_lang ON ps_product.id_product WHERE ps_product_lang.id_product = id_product");
$product = $stmt->fetchAll();

echo '<div class="table-responsive">';
echo '<table class="table table-striped">';
echo '<th>Checkbox</th><th>Product ID</th><th>Product Name</th><th>Price</th><th>Unit</th><th>Reference</th>';
foreach ($product as $row) {
print '<tr>';
print '<td><input type="checkbox" name="'. $row["id_product"] .'" value="'. $row["id_product"] .'"></td>';
print '<td>'. $row["id_product"] .'</td>';
print '<td>'. $row["name"] .'</td>';
print '<td>'. $row["price"] .'</td>';
print '<td>'. $row["unity"] .'</td>';
print '<td>'. $row["reference"] .'</td>';
print '</tr>';
echo '</table>';
echo '</div>';

catch(PDOException $e) {
echo "Error: " . $e->getMessage();

$conn = null;

Why is $row["name"] equal to nothing and why am I getting the error listed?

Answer Source

you have missed the ON clause in your join query and no need of where after applied ON. And the alias of ps_product was also missing before field name id_product. Please check query below. And if another fields are also common between those two tables then alias will also needed there.

$stmt = $conn->prepare("SELECT ps_product.id_product as id_product,name, price, unity, reference FROM ps_product JOIN ps_product_lang ON ps_product_lang.id_product = ps_product.id_product"); 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download