create-a-simple-database-app-connecting-to-mysql-with-php/

[utterances-bot]

Build a PHP & MySQL CRUD Database App From Scratch | Tania Rascia

In this tutorial, we're going to learn how to make the beginnings of a very simple database app, using PHP and MySQL. It will be half of a…

https://www.taniarascia.com/create-a-simple-database-app-connecting-to-mysql-with-php/

[kvijayarajan]

Hi Tania Rascia,

I am new to programming i had search so many CRUD code i am getting error since i am new to php cannot solve the issue but your code is very easy and good please post more and more Thank you, Vijay

[AyeniMichael]

This is literally the best beginner friendly php crud tutorial out there. Thanks a million

[codecopycoffee]

At the point where I'm trying to run install.php, I'm getting this error:

SQLSTATE[HY000] [2002] No route to host

Here are my files: init.sql:

CREATE DATABASE test;

use test;

CREATE TABLE users (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(50) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    notes VARCHAR(200)
    );

install.php

<?php

require "config.php";

try { $connection = new PDO("mysql:host=$host", $username, $password, $options); $sql = file_get_contents("data/init.sql"); $connection->exec($sql);

echo "Database and table users created successfully.";

} catch(PDOException $error) { echo "
" . $error->getMessage(); // I had to remove $sql here bc I was getting an error saying that the variable hadn't been declared }

config.php

<?php

/**

• Configuration for database connection

• */

$host = "192.168.64.2"; // My host isn't localhost, it's this $username = "root"; $password = ""; $dbname = "test"; $dsn = "mysql:host=$host;dbname=$dbname"; $options = array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION );

A few things I've tried

1. Using concatenation in config and install, for example: $dsn = "mysql:host=" . $host . ";dbname=" . $dbname;

2. Restarting XAMPP after making any changes

3. Using/not using a password

4. Not using init as a separate file and instead just putting the CREATE DATABASE line directly into $sql in install.php

If anyone has any suggestions for what else I might try, I would be very grateful!

[codecopycoffee]

Figured out I was making a silly mistake, but can't delete my comment. If you're reading this and you have at some point made a silly mistake, I hope it makes you feel less alone!

[taniarascia]

@codecopycoffee You can always share what the mistake was so people in the future can benefit. :) Wisdom of the Ancients.

[torbengb]

Hi Tania! This tutorial was a special pleasure to read and follow - you should do this kind of stuff professionally! Unfortunately I am getting http error 500 results when I submit the 'create' and find' forms:

I tried locally with MAMP and then on my web host, and I tried with your exact code copy-pasted. The 'install.php' runs fine on both platforms and generates the database and the table, and I am able to insert rows using an SQL client (dbeaver).

So I have faith in the platforms and the install code, but because i don't know the first thing about PHP, I am unable to troubleshoot the problem with the two forms. Any tips for me?

• Greetings from Austria!

[torbengb]

Update: I fixed it.

After posting the above, I compared my code to your GitHub repo, and that's when I noticed that I had my directory structure wrong: 'data/', 'common.php', 'config.php', and 'install.php' were inside 'public/'! I must have missed that distinction in your tutorial, and looking through it now I find that it's not very obvious.

I'm not sure I could give you any suggestion to add some emphasis on the directory structure, and perhaps it's just a silly mistake ot make. Thanks again for a great tutorial!

[codecopycoffee]

@torbengb I'm not Tania (obviously), but having done a lot of troubleshooting recently, I thought I'd chime in. A 500 error is a server error, and since the way the connection is written is different between install.php and the create/read files, my guess would be that the issue is either somewhere in your config file or with the way you've written the $connection code in read/create specifically. My own issue actually came from copying and pasting and not being careful about where closing tags were, so especially if you've tried that, I'd suggest double checking that you're actually opening and closing everything (including line endings) where you think are.

[codecopycoffee]

@torbengb Just saw your update, glad you fixed it! :)

[torbengb]

@codecopycoffee This is what I am building: http://toolpool-dev.golfbravo.net (still early prototype!)

[sheenemill]

Hi, Having an issue at the end of step 3. I can connect to my db using SQLyog... but when i try to send data from create.php i get a browser error...

This page isn’t workinglocalhost is currently unable to handle this request. HTTP ERROR 500

any idea?

great tutorial btw. i've never touched php and i'm finding the steps great!

[sheenemill]

I should read other comments first!!! I put in the wrong directory also

[codecopycoffee]

@torbengb That is so cool! A really original (and useful!) project :)

[torbengb]

Thank you @codecopycoffee, I am delighted to hear this :-D Let's see if I can get it off the ground. Who knows, it might become the next big thing?

[sheenemill]

Thank you @codecopycoffee, I am delighted to hear this :-D Let's see if I can get it off the ground. Who knows, it might become the next big thing?

As a beginner, I know very little about php... but would you consider your example a typical way that a developer would achieve the result? I’m thinking from a security point etc (I appreciate that there is no login). Or would several more things need to be implemented?

[torbengb]

@sheenemill I too am very new to development. I have been in the IT field for decades but never as a developer. This is my first attempt at PHP, rnd my first attempt at something of this size.

So I am not qualified to say what a typical project would look like. The lack of login functionality is intentional: I am focusing on the core features first, and will add login at a later date. I am learning as I go, one step at a time.

On Wed, 23 Sep 2020, 01:03 sheenemill, notifications@github.com wrote:

Thank you @codecopycoffee https://github.com/codecopycoffee, I am delighted to hear this :-D Let's see if I can get it off the ground. Who knows, it might become the next big thing?

As a beginner, I know very little about php... but would you consider your example a typical way that a developer would achieve the result? I’m thinking from a security point etc (I appreciate that there is no login). Or would several more things need to be implemented?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/taniarascia/comments/issues/21#issuecomment-697027055, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAGF4XVEM2NSPZIAXWFYGCTSHEUK5ANCNFSM4QDARVSQ .

[DrWongKC]

You write in a manner which makes it easier to understand yet it's a lot of information. Thank you for writing this. You're a great teacher!

[jdgnick]

Tania, I am trying this for the first time, thank you for this tutorial.

I have made it through the tutorial and get to the point where I enter my first user, click submit, and I get nothing - when I click submit it only shows a blank page. The entered information does not post to the user table. (I am looking at the contents using HeidiSQL.)

I am not sure if I failed to connect connect the database, or if I should get an error. Is there a simple supplemental code I can use on the create page to test the submit button to see if that is broken? I am not sure where to begin troubleshooting the issue.

I did manually insert two lines of data in the user table. When I go to the read page, it does not find the entries and similarly only produces a blank white page.

Thank you.

[jdgnick]

Update with additional info: I am using Windows 10. Firefox was giving me the blank page, Chrome is giving me an error 500. Since this is happening on the create and read page, it seems related to the submit button more than the content of the create/read query.

I did find an error this morning that was of my own doing, obviously, MAMP was wanting to use port 3307 because 3306 was being used. I had to append my config file to "localhost:3307" to use the correct connection. I have no idea if that is 'legal' or if it is affecting the submit button in another way. I have verified the install.php works fine to confirm the config $user name and $password and created three additional tables with different names just to be sure.

I still cannot create or read.

[jdgnick]

Update: since others may look here, this might be a prerequisites issue. I was of the belief that MAMP supported the PDO method. It seems it might be a separate piece of software or might require some type of activation. I jumped to part 2 and created update.php. Nothing appeared which leads me to believe it is a PDO connection issue. I can create the tables in the database indicating the config information is correct, but I cannot connect to the database when it is needed.

[jdgnick]

Update: Looking at the MAMP welcome page the PDO is already enabled. That feature is enabled by default. I do not know where to go from here.

[jdgnick]

I used the following try and my connection displays True, an intentional misspelled password returns False. So something is working.

try{
$dbh = new pdo( 'mysql:host=localhost:3308;dbname=test',
                'root',
                'root',
                array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
die(json_encode(array('outcome' => true)));
}
catch(PDOException $ex){
die(json_encode(array('outcome' => false, 'message' => 'Unable to connect')));
}

(After an uninstall and reinstall, my port is now 3008 where I was on 3007 previously.)

When I click on the submit button from the create page I still get a blank white page.

[jdgnick]

For posterity ... you know that point where after 15 hours of really trying to understand something that you just need SOMETHING to work, well I found another CRUD tutorial on tutorialrepublic.com. Amazingly enough I was still getting the same (stupid) blank page. Fearing the port :3308 was a problem I decided to figure that out first. The part of this message that may or may not be right but could warrant investigation for similar users is that during my initial learning experiments I installed a giant MySQL download that may be enabling another server that took over port 3306. In task manager I stopped the two processes for MySQL. Subsequently I was able to use the defaults in MAMP 80 and 3306 and leave my config file referring to localhost not localhost:3008. I guess this could be related to a firewall issue. In any event, the tutorial from tutorialrepublic.com immediately (after a MAMP restart) showed the proper forms and allowed me to submit and retrieve (C and R) and I felt like something was accomplished. However... when I came back to this tutorial, changed my db location to local host and submitted on the create page - nothing - blank white page again.

The biggest immediate difference I see is that they are connecting using

 $link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);

My reasoning my not be accurate, but it seems I have come back to this issue with the PDO connection. In any event, I would love to come back find some type of Eureka comment that can help me figure out why this tutorial did not work for me. As an absolute beginner, this tutorial gave me the taste and experience I needed to push forward. Thanks for following along.

[taniarascia]

@jdgnick I'm sorry you're having such issues. PHP and MySQL is a big pain to set up, especially for the first time. It sounds like much of your issues are environment related, which is outside of the scope of the tutorial. Once you're sure that's all set and PHP/MySQL are guaranteed to be set up and communicating properly with each other, whenever you see a blank, page, you can put this at the top of your script:

error_reporting(E_ALL);
ini_set('display_errors', '1');

It will show you error messages and help figure out where the error is originating.

[codecopycoffee]

@jdgnick I had the same issue with a different tutorial - connecting with mysqli worked, PDO didn't. At the time I had MAMP set up as a virtual machine instead of nested in my local file structure. I'm not sure what your setup is like, but iff you can avoid using MAMP as a VM, I would, because it adds a whole layer of complexity you don't need to worry about in the beginning.

A few other suggestions that helped when I had connection issues: 1. I think you might need to capitalize PDO like so: $dbh = new PDO( 2. Try not using a password and just using an empty string (wouldn't do that normally, but just because it's local). 3. Copy and paste everything from your config file into the create or read file, just to make sure it's not the require statement or some connection between the files that's creating a problem.

You got this!!

[codecopycoffee]

Nope, just tested, the PDO capitalization doesn't matter. You still got this!

[jdgnick]

Final update with issue resolution.

@taniarascia, the error reporting code you provided sent me directly to the problem. Thank you. There were extra characters in the common.php file that caused everything to break. As soon as I removed the extra stuff, everything worked perfect. I guess the message to share is if things are not working, scrap your local files and use all of the posted code on GitHub.

@codecopycoffee Thank you for following along and offering a suggestion. I appreciate the extra support you sent my way. Yay. :-)

[gbohare]

hello tania, This is Fantastic tutorial. All went so smooth for me till read.php. I think i have made a very silly mistake that i am unable to solve ,cant get any result . I think else loop is only there which is running because of the false condition of if. can't solve it...

[lilocruz]

Thanks so much for your effort.

[EliSko]

Thanks for this tutorial.

Is the addition of the closing tags at the end of header.php near the beginning just a typo / artifact of a coding tool? It seems to me that you want the 'andfromfooter.php` and not have them already in the header....

[EliSko]

Thanks for this tutorial.

Is the addition of the closing tags at the end of header.php near the beginning just a typo / artifact of a coding tool? It seems to me that you want to use the < /body > and < /html > tags from footer.php and not have them already prematurely in the header....

[EliSko]

Other errata:

• no close to the < ?php tag in common.php :point_right: should add ?> at the end
• in the final version of public/create.php between the submit handling PHP and the HTML form, the line with the "successfully added" message begins with a gratuitous angle bracket :point_right: should be removed
• the "successfully added" message in the final version lacks the escape() function call added earlier
• the "successfully added" message still appears even when certain database errors (like non-numeric value for Age) occur :point_right: should set $statement to FALSE in catch block
• data validation should always be done, like checking for numeric age (I get that it complicates things, but the control flow structure is necessary if you want to give a good example for people to build on) I recommend putting in a single test (like only digits in the Age field) to show how and where it's done including setting a flag, and then throw out ideas for enhancements like using the flag for tagging the label of the erroneous field in red without actually showing the code . . . this also gives the interested student points of departure for playing with the code
• similar to the first "successfully added" message above, the "No results" message line begins with a gratuitous angle bracket that :point_right: should be removed

[WWE-Corey]

I just finished part 1 & 2 and am extremely thankful for your time putting this together. Diving into this has been on my bucket list for years. I have a lot to learn yet but this gives me some insight into creating my own personal databases to continue learning.

[coderkkk]

Thank you tania for explaining them in such a nice way.

[Mulukbir]

I was surfing the web everyday in need of programming with PHP tutorials and none of them is successful. This day, thank you Taniarascia, I got a clear and precise PHP tutorial. Thank you again for spending your time to prepare the tutorial and initiating me to be a hopeful PHP programmer.

[ghost]

Thank you Tania for a useful primer for PHP and PDO. I am 83 and using this sort of exercise to learn about programming keeps the little grey cells in good condition. My IT career ended some 30 years ago and I was never a programmer, but held some management posts in various organisations, mainly on the support, sales and consultancy side of IT. I assume that the licence you publish this tutorial and data will allow me to use it to extend your work, for my own non-commercial benefit. Thanks again for the tutorial.

[nagaharish5]

Create a directory called data/ and create a file called init.sql. This will be our database initializing code.

i am unable to it, here this line

Create a directory called data/ and create a file called init.sql. This will be our database initializing code. i didnt understand it ,

but created a test db as below in my free aws managed db using workbench

CREATE DATABASE test;

use test;

CREATE TABLE users ( id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50) NOT NULL, age INT(3), location VARCHAR(50), date TIMESTAMP )

now how to create the folder so that

Now it's time to put that SQL code we created earlier to use. We'll be placing the contents of the data/init.sql file into a variable using the file_get_contents() function, and executing it with the exec() function.

$sql = file_get_contents("data/init.sql"); $connection->exec($sql);

this can be resolved

[kevinwells22]

For those having 500 errors, I found this fixed it, not sure why: Change to this in create.php:

require "config.php"; require "common.php";

[kevinwells22]

ahh, then I realised I had the folder structure all wrong, sorry.

[rawhunger]

Hi there!

I'm not sure what I did, but my CSS formatting is not working at all. I believe I have the header code correct, but there is no formatting of the rows. What could be the issue? Thanks.

[oxoxoxIxoxoxo]

I have a question about in which directory you put in the install.php file? If I go on from that directory, I just put in the SQL-Stmt, I would go on in the data directory. Is that right?

[oxoxoxIxoxoxo]

The Timestamp won't do its job on my SQL server. Is there a reason for that?

[oxoxoxIxoxoxo]

when you create the timestamp column, you will have to add ",..., date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);" at least it was with me like that