Advertisement:

Author Topic: php/mysql code help  (Read 4505 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

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 7,858
  • 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: 69,442
    • StoryBB/StoryBB 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...
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

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: 69,442
    • StoryBB/StoryBB 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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

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.