Advertisement:

Author Topic: php/mysql code help  (Read 3457 times)

Offline The Wizard

  • Sr. Member
  • ****
  • Posts: 855
php/mysql code help
« on: April 30, 2017, 11:34:49 AM »
Hello:

I have been away for a long time (real life stuff), and have forgotten  a lot of what I learned of php/mysql.
Below is the code I have been working on, and was hoping if someone could have a look at it and help make it smoother.
The code works I just know it could be better written.

Thanks

Wizard

Quote
<?
// Server Info
   $servername   = "localhost";
   $username   = "username";
   $password           = "password";
   $dbname      = "dbname";

// Gets Guide Input Info and removes troubling apostrophes
   $series_name      = str_replace("'", " ", $_POST['series_name']);
   $number_of_series   = str_replace("'", " ", $_POST['number_of_series']);
   $series_airdate      = str_replace("'", " ", $_POST['series_airdate']);
   $number_of_movies   = str_replace("'", " ", $_POST['number_of_movies']);
   $banner_url      = str_replace("'", " ", $_POST['banner_url']);
   $premise         = str_replace("'", " ", $_POST['premise']);

   $actors_name_1      = str_replace("'", " ", $_POST['actors_name_1']);
   $actors_name_2      = str_replace("'", " ", $_POST['actors_name_2']);
   $actors_name_3      = str_replace("'", " ", $_POST['actors_name_3']);
   $actors_name_4      = str_replace("'", " ", $_POST['actors_name_4']);
   $actors_name_5      = str_replace("'", " ", $_POST['actors_name_5']);
   $actors_name_6      = str_replace("'", " ", $_POST['actors_name_6']);
   $actors_name_7      = str_replace("'", " ", $_POST['actors_name_7']);
   $actors_name_8      = str_replace("'", " ", $_POST['actors_name_8']);
   $actors_name_9      = str_replace("'", " ", $_POST['actors_name_9']);
   $actors_name_10      = str_replace("'", " ", $_POST['actors_name_10']);

   $character_name_1   = str_replace("'", " ", $_POST['character_name_1']);
   $character_name_2   = str_replace("'", " ", $_POST['character_name_2']);
   $character_name_3   = str_replace("'", " ", $_POST['character_name_3']);
   $character_name_4   = str_replace("'", " ", $_POST['character_name_4']);
   $character_name_5   = str_replace("'", " ", $_POST['character_name_5']);
   $character_name_6   = str_replace("'", " ", $_POST['character_name_6']);
   $character_name_7   = str_replace("'", " ", $_POST['character_name_7']);
   $character_name_8   = str_replace("'", " ", $_POST['character_name_8']);
   $character_name_9   = str_replace("'", " ", $_POST['character_name_9']);
   $character_name_10   = str_replace("'", " ", $_POST['character_name_10']);

// Create connection
   $conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
   if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
   }

   $sql = "INSERT INTO EPGuide.Series (
         id, series_name,
         number_of_series, series_airdate,
         number_of_movies, banner_url,
         character_name_1, actors_name_1,
         character_name_2, actors_name_2,
         character_name_3, actors_name_3,
         character_name_4, actors_name_4,
         character_name_5, actors_name_5,
         character_name_6, actors_name_6,
         character_name_7, actors_name_7,
         character_name_8, actors_name_8,
         character_name_9, actors_name_9,
         character_name_10, actors_name_10,
         premise)
         VALUES (NULL, '$series_name',
         '$number_of_series', '$series_airdate',
         '$number_of_movies', '$banner_url',
         '$character_name_1', '$actors_name_1',
         '$character_name_2', '$actors_name_2',
         '$character_name_3', '$actors_name_3',
         '$character_name_4', '$actors_name_4',
         '$character_name_5', '$actors_name_5',
         '$character_name_6', '$actors_name_6',
         '$character_name_7', '$actors_name_7',
         '$character_name_8', '$actors_name_8',
         '$character_name_9', '$actors_name_9',
         '$character_name_10', '$actors_name_10',
         '$premise');";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

?>
 

Offline Colin

  • Lead Developer
  • SMF Hero
  • *
  • Posts: 7,765
  • Gender: Male
  • SMF Developer
    • colinschoen on GitHub
"If everybody is thinking alike, then somebody is not thinking." - Gen. George S. Patton Jr.

Colin

Offline forumfan32

  • Semi-Newbie
  • *
  • Posts: 14
Re: php/mysql code help
« Reply #2 on: June 29, 2017, 03:30:38 PM »
You should be using prepared statements, as you are extremely susceptible to SQL injection. Also, I don't understand the reason for so many str_replaces. What does "troubling apostrophes" mean? If it is necessary, why isn't it in a for loop? It would make the code more concise.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 68,042
    • Arantor on GitHub
Re: php/mysql code help
« Reply #3 on: June 29, 2017, 04:18:33 PM »
The prepared statements would fix the "troubling apostrophes" problem automatically.

Also a loop isn't ideal here because it's all going into one row in the database, which is hard to build correct queries without the troubling apostrophes. But that would also veer toward having a stronger schema in the first place...
To assume is to hope that those who came before had the presence of mind and capacity to implement the dreams of those who would come after.

You either die a hero or live long enough to see yourself become the villain. It seems you have chosen which, and now I must do the same.

Offline forumfan32

  • Semi-Newbie
  • *
  • Posts: 14
Re: php/mysql code help
« Reply #4 on: July 06, 2017, 03:45:44 PM »
The prepared statements would fix the "troubling apostrophes" problem automatically.

Also a loop isn't ideal here because it's all going into one row in the database, which is hard to build correct queries without the troubling apostrophes. But that would also veer toward having a stronger schema in the first place...

Was really only talking about the the str_replace part where the variables are set, even though I didn't understand its purpose. Of course database queries should certainly not be in loops. The less database lookups the better.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 68,042
    • Arantor on GitHub
Re: php/mysql code help
« Reply #5 on: July 06, 2017, 03:47:38 PM »
He has all the str_replaces because of (and for no reason other than) not using prepared statements to get around the 'issues' of using bare data in SQL.
To assume is to hope that those who came before had the presence of mind and capacity to implement the dreams of those who would come after.

You either die a hero or live long enough to see yourself become the villain. It seems you have chosen which, and now I must do the same.

Offline forumfan32

  • Semi-Newbie
  • *
  • Posts: 14
Re: php/mysql code help
« Reply #6 on: July 06, 2017, 05:52:59 PM »
Ah ok, that makes sense. I was confused because I haven't seen non-prepared statements in years. Hopefully OP knows how to use them correctly. Let me know if not, The Wizard.