SQL & PHP - Adding to table, failing

Question

I successfully created a table in my database, using PHP. Now, I'm trying to fill it with data. When I var_dump the data I'm trying to add, it correctly renders - it's not undefined.

I don't get any errors, but there are no entries in my SQL tables. What did I do wrong? Thanks.

Database layout here:

foreach($x->channel->item as $entry) {

  if ($y < 8) {

      $con=mysqli_connect("localhost","usernameremoved",
        "passwordremoved","databasenameremoved");
      // Check connection
      if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
      mysqli_query($con,"INSERT INTO Entries (Link, Title)
      VALUES ($entry->link, $entry->title)");
      echo "Tables updated successfully.";
      mysqli_close($con); 


      $y++;

  }
}

UPDATE, for Watcher:

Parse error: syntax error, unexpected '$entry' (T_VARIABLE) in C:\xampp\htdocs\ (... ) \PHP\rss\index.php on line 60

if ($y < 8) {

  mysqli_query($con,"INSERT INTO Entries (Link, Title)
  VALUES ("$entry->link", "$entry->title")");
  echo "Tables updated successfully.";



  $y++;

}

do you really really need to connect and close to mysql each loop? that doesn't make sense — Kevin
– Kevin, Commented Sep 16, 2014 at 3:00
creating the result string inside the loop for a single insert will be far more efficient — user557846
– user557846, Commented Sep 16, 2014 at 3:01
Makes sure the $y < 8 condition is actually passing. You only seem to be incrementing $y if the condition already passes. — AlliterativeAlice
– AlliterativeAlice, Commented Sep 16, 2014 at 3:02
You also probably need to add quotes around the data that you're inserting. — Jeff Lambert
– Jeff Lambert, Commented Sep 16, 2014 at 3:04
Watcher, if I add quotes I get an error. If I use apostrophes, I still don't get any data into my tables. VALUES ('$entry->link', '$entry->title')"); — icor103
– icor103, Commented Sep 16, 2014 at 3:08

Sverri M. Olsen · Accepted Answer · 2014-09-16 03:18:43Z

1

This case is pretty much what prepared statements were created for.

// Database connection
$db = new MySQLi("localhost","usernameremoved", "passwordremoved","databasenameremoved");
if ($db->error) {
    echo "Failed to connect to MySQL: ".$db->error;
}
// Prepared statement
$stmt = $db->prepare('INSERT INTO entries (Link, Title) VALUES (?, ?)');
if ($stmt === false) {
    die('Could not prepare SQL: '.$db->error);
}
// Bind variables $link and $title to prepared statement
if ( ! $stmt->bind_param('ss', $link, $title)) {
    die('Could not bind params: '.$stmt->error);
}
$y = 0;
foreach ($x->channel->item as $entry) {
    if ($y >= 8) {
        break;
    }
    // Set values on bound variables
    $link  = $entry->link;
    $title = $entry->title;

    // Execute
    if ($stmt->execute() === false) {
        die('Could not execute query: '.$stmt->error);
    }
    $y++;
}
$stmt->close();
$db->close();

answered Sep 16, 2014 at 3:18

Sverri M. Olsen

13.2k3 gold badges38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

icor103 Over a year ago

I don't have enough rep to vote you up, so I can only offer my thanks here. :)

Kevin · Accepted Answer · 2014-09-16 05:05:49Z

1

Just take off that connect and close outside that loop. And as per Dagon, combine them into a multiple insert instead. Example:

$con = mysqli_connect("localhost","usernameremoved", "passwordremoved","databasenameremoved");
$stmt = 'INSERT INTO Entries (Link, Title) VALUES ';
$values = array();
$y = 0;
foreach ($x->channel->item as $entry) {
    if($y < 8) {
        $values[] = "('$entry->link', '$entry->title')";
    }
    $y++;
}
$values = implode(', ', $values);
$stmt .= $values;
mysqli_query($con, $stmt);
mysqli_close($con);

edited Sep 16, 2014 at 5:05

answered Sep 16, 2014 at 3:14

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

Collectives™ on Stack Overflow

SQL & PHP - Adding to table, failing

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related