6 Replies - 609 Views - Last Post: 14 October 2016 - 07:16 PM Rate Topic: -----

#1 AndreeU17  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 163
  • Joined: 29-June 13

Not understanding how PHP and MySQL work together?

Posted 14 October 2016 - 05:16 PM

I'm struggling getting a grip on PHP and MySQL. Many tutorials don't specify this important form of information thats repetitions.

I'm trying to create a my own version of minimalistic form blog. I have yet to begin designing and coding its (HTML/CSS) since i'm getting the databases ideas and concepts together. I'm utilizing MySQLi (OOP) version. The below code is from W3Schools, i'm using it for further understanding.
 
$servername = "localhost";
    $username = "username";
    $password = "password";

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



What i don't understand is, currently the server has no database, tables, etc. My next step would be to create a database.
$query = "CREATE DATABASE blogDB";
    if ($connect->query($query) === TRUE) {
        echo "Database created successfully";
    } else {
        echo "Error creating database: " . $connect->error;
    }


Then i proceed with creating a table and giving it columns and rows, etc. What i don't fully understand is, do i have to watch out for repetition with connectivity? What i'm saying is that everytime this page is called, from my understanding it will create a database and tables, etc. Or does MySQL Server/Engine knows not to recreate something that has already being created once. If thats not the case, then i must create some for of action that checks with the server to see whether their is already a database by the set name or tables, etc.

Or should all that be separate and should it only pertain to the php file that needs it? The code and all that fun stuff is understandable, i just cant grasp how it will work if i'm constantly calling the connect.php file. Also once, i execute new TABLES, DATABASE, etc. will it always be available or must i recreate it every time the server is turned off (if using within a localhost server like WAMP).

Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Not understanding how PHP and MySQL work together?

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13962
  • View blog
  • Posts: 55,726
  • Joined: 12-June 08

Re: Not understanding how PHP and MySQL work together?

Posted 14 October 2016 - 05:22 PM

You shouldn't be creating a database each time. At best you would have a 'setup script' (as most folks call it) that creates the tables, logins, etc.. Once that's done you remove it.. going forward you would have pages that do SELECT, INSERT, DELETE, UPDATE SQL queries to interact with the table.
Was This Post Helpful? 1
  • +
  • -

#3 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Not understanding how PHP and MySQL work together?

Posted 14 October 2016 - 05:24 PM

Typically you don't CREATE anything in code. The database and the table structure should be created once in the beginning - usually through some sort of one-time setup script, or simply through a GUI like MySQL Workbench or phpMyAdmin - and then used by the code.

MySQL is just a database server. It's job is to store data. It doesn't really care what kind of code/apps are using it. If you write a website that tries to CREATE a table each time somebody opens it, it'll do that once, and then just give you errors, because the table already exists.

Quote

Also once, i execute new TABLES, DATABASE, etc. will it always be available or must i recreate it every time the server is turned off (if using within a localhost server like WAMP).

No. A database server that doesn't keep the data beyond a server restart is worse than useless. Databases like MySQL keep the data, and the tables/databases that contain it, until you explicitly delete them.
Was This Post Helpful? 1
  • +
  • -

#4 AndreeU17  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 163
  • Joined: 29-June 13

Re: Not understanding how PHP and MySQL work together?

Posted 14 October 2016 - 05:42 PM

View Postmodi123_1, on 14 October 2016 - 06:22 PM, said:

You shouldn't be creating a database each time. At best you would have a 'setup script' (as most folks call it) that creates the tables, logins, etc.. Once that's done you remove it.. going forward you would have pages that do SELECT, INSERT, DELETE, UPDATE SQL queries to interact with the table.


Yeah i went ahead and tested the code, every time i refreshed i received this error "Error creating database: Can't create database 'blogDB'; database exists". Now what do you mean by setup script? Is that a simple, php file that is its own entity that will only be called once? Or is it as simple as me simply implementing the code, run once, make sure its all successful then erase that portion of the code? Then all my other PHP pages will like you mentioned only SELECT, INSERT, DELETE, UPDATE queries.

Thanks for the info, makes a lot more sense.

View PostAtli, on 14 October 2016 - 06:24 PM, said:

Typically you don't CREATE anything in code. The database and the table structure should be created once in the beginning - usually through some sort of one-time setup script, or simply through a GUI like MySQL Workbench or phpMyAdmin - and then used by the code.

MySQL is just a database server. It's job is to store data. It doesn't really care what kind of code/apps are using it. If you write a website that tries to CREATE a table each time somebody opens it, it'll do that once, and then just give you errors, because the table already exists.

Quote

Also once, i execute new TABLES, DATABASE, etc. will it always be available or must i recreate it every time the server is turned off (if using within a localhost server like WAMP).

No. A database server that doesn't keep the data beyond a server restart is worse than useless. Databases like MySQL keep the data, and the tables/databases that contain it, until you explicitly delete them.


I understand that PHPMyAdmin is a great tool by the PHP engine for database control, but i feel more comfortable manipulating my necessary tables within code rather than having to jump back and forth between PHP scripts and GUI's. I didn't know that MySQL will literally keep the data until told to be deleted. I read a few times, not sure where, but isn't deleting data from rows a bad programming habit for MySQL?

Thanks!
Was This Post Helpful? 0
  • +
  • -

#5 astonecipher  Icon User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2489
  • View blog
  • Posts: 9,970
  • Joined: 03-December 12

Re: Not understanding how PHP and MySQL work together?

Posted 14 October 2016 - 05:56 PM

You won't jump back and forth anyway. You create the scheme and it is done until you need to make a structure change.

And professionally, there are hard and soft deletes. Soft depends on a flag for displaying the data. Hard actually removes the record from a data table.
Was This Post Helpful? 0
  • +
  • -

#6 AndreeU17  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 163
  • Joined: 29-June 13

Re: Not understanding how PHP and MySQL work together?

Posted 14 October 2016 - 06:21 PM

View Postastonecipher, on 14 October 2016 - 06:56 PM, said:

You won't jump back and forth anyway. You create the scheme and it is done until you need to make a structure change.

And professionally, there are hard and soft deletes. Soft depends on a flag for displaying the data. Hard actually removes the record from a data table.


So ideally i can create the setup utilizing the GUI, then build the platform and in code simply Update/Delete, etc. The reason i was saying i would go back and forth is because, i could create a table utilizing php query code, then i'd go to the GUI to double check i've created a table rather than checking if the table is created using php code. I did try running the
"CREATE TABLE Blogs"
twice, and i received an error (mentioning how it already exists).

I completely understand now how it works. I was curious, the above user mentioned something about setup script needing to only be ran once. I was wondering what that would look like. Ideally i'm trying to create it in a way that it will validate whether the database is or isn't their, depending on the attendance of the table, it will either create or continue onwards. I feel comfortable validating everything for future debugging.

Any tips is appreciated since my first jump into PHP and MySQL (back-end dev).
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher  Icon User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2489
  • View blog
  • Posts: 9,970
  • Joined: 03-December 12

Re: Not understanding how PHP and MySQL work together?

Posted 14 October 2016 - 07:16 PM

Ideally, the tables and database will already be established. While setup scripts are fun, they are only useful when deploying your system to another server; on your own, you just don't need to.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1