Member Avatar for learner001

I have recently started to write server scripts. Although the question may seem to be very basic but i am not able to get a solution.
I have two tables one is PRODUCTS and the other is DEALS

PRODUCTS TABLE:

Id  product_name  product_desc   category    brand
1   product1      desc1          cat1        brand1                                                     
2   product2      desc2          cat2        brand2

DEALS TABLE

Id     deal_name      productid    
1      todayoffer        2

I wish to put the value of id (from PRODUCTS table) into productid and carry this value to DEALS table and then add deal_name along with it as shown above. The recent code that i am using is

<?
require_once('config.php'); //connection

$dealname=$_REQUEST['dealname'];
$productid=$_REQUEST['productid'];
$id=$_REQUEST['id'];
$productid=$_REQUEST['productid'];

$inserts = mysql_query("insert into deals (dealname,productid) values ('".$dealname."','".$productid."') SELECT productid FROM products WHERE productid = '".$id."'");

    $posts[0]['message'] = 'Deal Registered';
    $idd = mysql_insert_id();
    $selectt = mysql_query("select * from deals where id = '".$idd."'");

    $posts[0]['detail'] = $selectt;
    header('Content-type: application/json');
    echo json_encode($posts);
?>

I would really appreciate if anyone could guide me and tell me why i am not getting the result

  • 2 Contributors
  • 1 Reply
  • 245 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by Traevel

Dani AI

Generated

Good catch by about column names — the most common causes here are mismatched column names, an incorrect INSERT syntax, and the use of the old mysql_* API. The safest, future-proof fix is to validate the product id, use prepared statements, and insert the deal with a proper INSERT. Example (uses PDO and explicit validation):

<?php
// create or pull a PDO instance ($pdo). Replace DSN/creds as needed.
$pdo = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8mb4','dbuser','dbpass',[
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$dealName  = trim($_POST['dealname'] ?? '');
$productId = (int)($_POST['productid'] ?? 0);

if ($dealName === '' || $productId <= 0) {
  http_response_code(400);
  echo json_encode(['error'=>'dealname and productid required']);
  exit;
}

// ensure the product exists
$check = $pdo->prepare('SELECT id FROM products WHERE id = ?');
$check->execute([$productId]);
if (!$check->fetch()) {
  http_response_code(404);
  echo json_encode(['error'=>'product not found']);
  exit;
}

// insert and return the inserted row
$ins = $pdo->prepare('INSERT INTO deals (deal_name, productid) VALUES (:d, :p)');
$ins->execute([':d'=>$dealName, ':p'=>$productId]);

$dealId = $pdo->lastInsertId();
$row = $pdo->prepare('SELECT * FROM deals WHERE id = ?');
$row->execute([$dealId]);
echo json_encode(['message'=>'Deal Registered','detail'=>$row->fetch()]);

Troubleshooting tips: enable exceptions (PDO::ERRMODE_EXCEPTION) so you see real errors in logs; confirm exact column names (e.g., deal_name vs dealname); cast numeric IDs with intval before using them; and never interpolate user input directly into SQL. As an alternative you can do a single-statement guarded insert:

INSERT INTO deals (deal_name, productid) SELECT :deal, id FROM products WHERE id = :id

Finally, consider using InnoDB with a foreign-key constraint on deals.productid -> products.id so database integrity prevents orphaned deals.

Member Avatar for Traevel

Well at first glance, in insert into deals (dealname,productid) you are using dealname and not deal_name. Also, where you are using SELECT productid FROM products WHERE productid = '".$id."'"); you are twice referring to a column productid while the table products only has id, according to your description of the table layout.

Also, it could be that you are passing $productid as text and not as a number because of the single quotes. Similar problem with $id and $idd (which you are also comparing to id instead of Id by the way).

You could try printing the results of mysql_query to see whether the query fails or not. If you have access to the database itself via a console or phpmyadmin for instance you could test the queries using some default values. That way you'll be sure the queries themselves are valid, and whether or not the column/table names are indeed correct, before debugging the code.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.