Update multi table in mysql

Question

I'm trying to update mysql multiple tables, although there's no error but the changes are not reflected in the mysql database. Please let me if i would need to use JOIN to put the query update within one string.

Do I also still need

mysqli_query($conn, $sqlCD) or die(mysqli_error($conn)); in the below ? 

  $pCDTitle = filter_has_var(INPUT_GET, 'CDTitle') ? $_GET['CDTitle']: null;         // store all parameter in variable
  $pCDPubName = filter_has_var(INPUT_GET, 'CDPub') ? $_GET['CDPub']: null;
  $pCDYear = filter_has_var(INPUT_GET, 'CDYear') ? $_GET['CDYear']: null;
  $pCDCategory = filter_has_var(INPUT_GET, 'CDCat') ? $_GET['CDCat']: null;        
  $pCDPrice = filter_has_var(INPUT_GET, 'CDPrice') ? $_GET['CDPrice']: null;
  $pCDID = filter_has_var(INPUT_GET, 'CDID') ? $_GET['CDID']: null;
  $pCDPubID = filter_has_var(INPUT_GET, 'pubID') ? $_GET['pubID']: null;    

$sqlCD = mysql_query("UPDATE nmc_cd SET CDTitle='$pCDTitle', CDYear='$pCDYear', CDPrice='$pCDPrice'");
$sqlCD2 = mysql_query("UPDATE nmc_category SET catDesc='$pCDCategory'");
$sqlCD3 = mysql_query("UPDATE nmc_publisher SET pubName='$pCDPubName'");

//mysqli_query($conn, $sqlCD) or die(mysqli_error($conn));
//mysqli_query($conn, $sqlCD2) or die(mysqli_error($conn));
//mysqli_query($conn, $sqlCD3) or die(mysqli_error($conn));
mysqli_close($conn);

score 3 · Answer 1 · answered Nov 27 '15 at 05:17

You have to change the query:

$sqlCD = mysql_query("UPDATE nmc_cd SET CDTitle=$pCDTitle, CDYear=$pCDYear, CDPrice=$pCDPrice");
$sqlCD2 = mysql_query("UPDATE nmc_category SET catDesc=$pCDCategory");
$sqlCD3 = mysql_query("UPDATE nmc_publisher SET pubName=$pCDPubName");

Reason: If a string in Double qoutes "" have a php variable like $test the its value is automatically in the string but if you use single qoute '' then its the variable name prints. For example:

$test = 5;
echo "The number is $test"; //The number is 5 prints
echo 'The number is $test'; //The number is $test prints

Virendra Nagda · Answer 2 · 2015-11-27T06:27:30.147

mysqli_query($conn, $sqlCD) or die(mysqli_error($conn)); in the below ? 

  $pCDTitle = filter_has_var(INPUT_GET, 'CDTitle') ? $_GET['CDTitle']: null;         // store all parameter in variable
  $pCDPubName = filter_has_var(INPUT_GET, 'CDPub') ? $_GET['CDPub']: null;
  $pCDYear = filter_has_var(INPUT_GET, 'CDYear') ? $_GET['CDYear']: null;
  $pCDCategory = filter_has_var(INPUT_GET, 'CDCat') ? $_GET['CDCat']: null;        
  $pCDPrice = filter_has_var(INPUT_GET, 'CDPrice') ? $_GET['CDPrice']: null;
  $pCDID = filter_has_var(INPUT_GET, 'CDID') ? $_GET['CDID']: null;
  $pCDPubID = filter_has_var(INPUT_GET, 'pubID') ? $_GET['pubID']: null;    

$sqlCD = mysqli_query($conn,"UPDATE nmc_cd SET CDTitle='$pCDTitle', CDYear='$pCDYear', CDPrice='$pCDPrice'") or die(mysqli_error($conn));
$sqlCD2 = mysqli_query($conn,"UPDATE nmc_category SET catDesc='$pCDCategory'") or die(mysqli_error($conn));
$sqlCD3 = mysqli_query($conn,"UPDATE nmc_publisher SET pubName='$pCDPubName'") or die(mysqli_error($conn));
mysqli_close($conn);

try this.

Thanks but realize it replace the entire database.. i think it's lacking the row ID and directing it to only update the selected row... — Doran L, Nov 27 '15 at 06:36

Update multi table in mysql

2 Answers2