PHP MYSQL Select and Insert but not work

Question

I have 2 table: "customer" and "data". I want to select from "customer" with mysqli_fetch_array() and insert some data to "data" but I can't insert with this code please help me.

<?php
    ini_set('display_errors', 1);
    error_reporting(~0);

    $serverName = "localhost";
    $userName = "root";
    $userPassword = "root";
    $dbName = "mydatabase";

    $conn = mysqli_connect($serverName,$userName,$userPassword,$dbName);

    $sql = "SELECT * FROM customer";

    $query = mysqli_query($conn,$sql);

?>
<table width="600" border="1">
  <tr>
    <th width="91"> <div align="center">URL </div></th>
    <th width="98"> <div align="center">Board </div></th>
    <
  </tr>
<?php
while($result=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
?>
  <tr>
    <td><div align="center"><?php echo $result[$objResult["url"];?></div></td>
    <td><?php echo $result["board"];?></td>

  </tr>
<?php

$sql2 = "INSERT INTO data (url,board) VALUES ('".$result["url"]."','".$result["board"]."')";

    $query2 = mysqli_query($conn,$sql2);


}
?>
</table>
<?php
mysqli_close($conn);
?>

Do fake, hardcoded values into that statement and see if it inserts, like: `"INSERT INTO data (url,board) VALUES ('test','test')";`. If it does, then you know it's what you are inserting that is the problem. — Rasclatt, Feb 24 '16 at 04:09
Also do `print_r($result);` See if it's what you are expecting. — Rasclatt, Feb 24 '16 at 04:10
Last, check the columns in your database, make sure they are spelled correctly, that they can accommodate the amount of characters and that they are the correct type (`varchar` vs `int` for example). There is a lot of troubleshooting you can do. — Rasclatt, Feb 24 '16 at 04:12
@Rasclatt It can insert first time but second time can't insert. So,if table "data" is empty of data,It can insert but if not empty it can't insert — sammy, Feb 24 '16 at 04:24
Have you tried using `mysqli_error($conn);` to see if there is a mysql error? — Rasclatt, Feb 24 '16 at 04:30
Have you tried using the multi-query: http://stackoverflow.com/questions/10924127/two-mysqli-queries I am just guessing, I use PDO so I can't really definitively say. — Rasclatt, Feb 24 '16 at 04:57

Punit Gajjar · Answer 1 · 2016-02-24T05:17:54.743

0

I found 1 syntax error in your code.

You have error here

<?php echo $result[$objResult["url"];?>

which should be

<?php echo $result["url"];?>

check my code.

<?php
    ini_set('display_errors', 1);
    error_reporting(~0);

    $serverName = "localhost";
    $userName = "root";
    $userPassword = "";
    $dbName = "mydatabase";

    $conn = mysqli_connect($serverName,$userName,$userPassword,$dbName);

    $sql = "SELECT * FROM customer";

    $query = mysqli_query($conn,$sql);
?>
<table width="600" border="1">
  <tr>
    <th width="91"> <div align="center">URL </div></th>
    <th width="98"> <div align="center">Board </div></th>
      </tr>
<?php
while($result=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
?>
  <tr>
    <td>
        <div align="center">
        <?php echo $result["url"];?>
        </div>
    </td>
    <td><?php echo $result["board"];?></td>

  </tr>
<?php

$sql2 = "INSERT INTO data (url,board) VALUES ('".$result["url"]."','".$result["board"]."')";
$query2 = mysqli_query($conn,$sql2);
}
?>
</table>
<?php
mysqli_close($conn);
?>

dont forget to change your connection parameters.

here is the SQL Script :

DROP TABLE IF EXISTS customer; CREATE TABLE customer ( id int(11) NOT NULL AUTO_INCREMENT, url varchar(255) DEFAULT NULL, board varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- Records of customer

INSERT INTO customer VALUES ('1', 'First URL', 'Bord 1'); INSERT INTO customer VALUES ('2', 'Second URL', 'Board 2'); INSERT INTO customer VALUES ('3', null, null);

-- Table structure for data

DROP TABLE IF EXISTS data; CREATE TABLE data ( id int(11) NOT NULL AUTO_INCREMENT, url varchar(255) DEFAULT NULL, board varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

-- Records of data

INSERT INTO data VALUES ('1', 'First URL', 'Bord 1'); INSERT INTO data VALUES ('2', 'First URL', 'Bord 1'); INSERT INTO data VALUES ('3', 'Second URL', 'Board 2'); INSERT INTO data VALUES ('4', 'First URL', 'Bord 1'); INSERT INTO data VALUES ('5', 'Second URL', 'Board 2'); INSERT INTO data VALUES ('6', 'First URL', 'Bord 1'); INSERT INTO data VALUES ('7', 'Second URL', 'Board 2'); INSERT INTO data VALUES ('8', 'First URL', 'Bord 1'); INSERT INTO data VALUES ('9', 'Second URL', 'Board 2'); INSERT INTO data VALUES ('10', '', '');

edited Feb 24 '16 at 05:17

answered Feb 24 '16 at 05:01

Punit Gajjar

4,937
7
35
70

True, if "data" is empty it can insert but now empty can't insert :) – sammy Feb 24 '16 at 05:10
it depends of your table design if it allow inserting null value. still let me check – Punit Gajjar Feb 24 '16 at 05:14
in my case . i have added blank value in customer table , and its working . it allows me to insert blank value in the table "data". – Punit Gajjar Feb 24 '16 at 05:15
let me add the SQL script , you will get better idea , i have just edited the answer , please check it – Punit Gajjar Feb 24 '16 at 05:17
this is data sturcture. CREATE TABLE IF NOT EXISTS `data` ( `no` int(11) NOT NULL AUTO_INCREMENT, `user` varchar(20) NOT NULL, `url` varchar(150) NOT NULL, `board` int(20) NOT NULL, `topic` int(10) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`user`), KEY `no` (`no`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ; – sammy Feb 24 '16 at 05:23
this is customer structure.CREATE TABLE IF NOT EXISTS `customer` ( `no` int(11) NOT NULL AUTO_INCREMENT, `url` varchar(100) NOT NULL, `board` int(200) NOT NULL, `pr` int(200) NOT NULL, `da` int(200) NOT NULL, `smf` int(11) NOT NULL, PRIMARY KEY (`no`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ; – sammy Feb 24 '16 at 05:26
actually problem is with your Primary key.. In data table your primary key is user column. keep id as your primary key, and it should be auto increment, your problem will be resolved for sure. – Punit Gajjar Feb 24 '16 at 05:27
I will try and tell result to you soon. – sammy Feb 24 '16 at 05:39
Finally, I can solve this problem thank you everybody. – sammy Feb 24 '16 at 05:55
I set same primary key in 2 table :) – sammy Feb 24 '16 at 07:15

PHP MYSQL Select and Insert but not work

1 Answers1