php with a database on xampp

Invidia · September 18, 2015

I had a different account on here but forgot my info. Sorry about that. Anyway.

I'm a programming student with a teacher that doesn't really teach. She just reads pre-made powerpoint slides and often seems surprised to learn about things that she was unaware you could do prior to reading the slides. Basically, I think the school needed a PHP teacher and just kinda said "Eh. close enough. You'll do." She's not really friendly and asking for help is usually met with a rude reply, a condescending tone and an answer that isn't really an answer. I've asked classmates for help to no avail - they're just as confused. There are literally no PHP tutors as it's just a community college. I had the same lady for SQL and knew what to expect, but she's the only professor for these classes. She's an ex-marine, she's probably in her 60s or so and is close to retiring so I kinda get the feeling that she doesn't really care at this point, but it does a huge disservice to us - her students. I've never actually seen her code a single thing in class and she often gets confused and has to stop to thumb through her book or power points in order for her to figure out how to even import a database into xampp just to show us what it looks like. So this is my last hope. My chosen major is programming, like OOP with VB and Java and such, but we're still required to take PHP and SQL. I'm working on talking to the dean about the issues with the prof, but until something if anything can be done I have to deal with what's at hand.

With that said, here my assignment specifications just so you all have an idea what I'm actually trying to do. Below I'll post code showing what I've done so far. I'm very confused, very frustrated and struggling so much with this thing. Please understand, I'm not asking anyone to do my homework for me. I need help. I have some random code snippets garnered from my book and the crap power-point, but I don't understand how they work or where to put them or anything.

==============================================

INSTRUCTIONS

Deliverables: Zipped project folder containing the following files:

Index.html containing a form

Main.css for formatting

Display_data.php to display the data retrieved from the database

Database_error.php to display error messages

If necessary:

Use the SQL tab in PHPAdmin to write and run the following query:

CREATE DATABASE ZipcodeData

Use the Import tab to import the file zipcodes2014 to create the tables and import the data for the ZipcodeData database.

Create an HTML file with a form that allows a user to enter a city and a two letter state abbreviation. Use POST and your display_data.php file.

Create a PHP file that will create and run a query against the zipcodes table. The query will retrieve the following columns from the table, for the city and state entered in the form:

Zipcode

Latitude

Longitude

Estimated population

Try/Catch must be used around the following pieces of code:

Creating the PDO object to connect to a database

Preparing, binding values and executing the query

Each Catch clause will assign a different appropriate message to a variable, and will then include the database_error.php file (which will display the error message) and exit display_data.php.

The HTML page created and returned by the PHP will contain the city and state as <h1> headers. The PHP must display the four columns of data in a table. Because the number of rows to be returned may be different each time the code should use a foreach loop that will loop through the array returned by the query, creating a new row for the table and displaying the data for each iteration of the loop.

===========================================

MY HTML PAGE

<!--Indicates page is HTML5 compliant-->
<!DOCTYPE html>
<html>
<head>
    <title>Find Zipcode Data</title>
    <link rel="stylesheet" type="text/css" href="main.css">
</head>


<body>
    <main>


        <h1>Zip code data by city and state</h1>


        <form action="Display_data.php" method="post">


<!-- User input labels -->
            <div id="data">
                <label>City:</label>
                <input type="text" name="city"><br>


                <label>State (two letter abbreviation):</label>
                <input type="text" name="state"><br>
            </div>


<!-- Submit and Reset buttons -->
            <div id="buttons">
                <label> </label>
                <input type="submit" value="Display Zipcodes"><br>
                <input type="reset" value="Clear Form"><br>
            </div>


        </form>
    </main>
</body>
</html>

==============================================

MY CSS PAGE

/*CSS for general page look*/
main {
    width: 450px;
height: 190px;
    margin: 0 auto;
    padding: 1em;
    background: white;
    border: 2px solid navy;
}


body {
    background-color: #fff;
font-family: Arial, Helvetica, sans-serif;
text-align: center
}




/* Header */ 
h1 {
margin-top: 0;
color: navy;
}


/*CSS for label positioning*/
label {
    width: 12em;
    float: left;
    padding-bottom: .5em;
}


/*corresponds to div tag on index.html to adjust spacing around textboxes*/
#data input {
    float: left;
    width: 11em;
    margin-bottom: .5em;
}


/*corresponds to div tag on index.html to adjust spacing below buttons*/
#buttons input {
    margin-bottom: .5em;
   left:50%;
}

===========================================

MY DISPLAY_DATA.PHP PAGE

<?php
$city = $_POST['city'];
$state = $_POST['state'];


$dsn = 'mysql:host=localhost;dbname=zipcodedata';
$username = 'mgs_user';
$password = 'pa55word';


try {
    $db = new PDO($dsn, $username, $password);
    echo '<p>You are connected to the database!</p>';
} catch (PDOException $e) {
    $error_message = $e->getMessage();
    echo "<p>An error occurred while connecting to
             the database: $error_message </p>";
exit();
}


// creates PDO object
$db = new PDO($dsn, $username, $password);


?>


<!--Indicates page is HTML5 compliant-->
<!DOCTYPE html>
<html>
<head>
<!--Titles the page at the top such as on the browser tab (w/ Chrome)-->
    <title>Display zip code data</title>
<!--Pulls the CSS styling from the main.css page-->
    <link rel="stylesheet" type="text/css" href="main.css">
</head>
<body>
    <main>


    </main>
</body>
</html>

======================================

MY DATABASE_ERROR.PHP PAGE

<?php


?>


<!--Indicates page is HTML5 compliant-->
<!DOCTYPE html>
<html>
<head>
<!--Titles the page at the top such as on the browser tab (w/ Chrome)-->
    <title>Error</title>
<!--Pulls the CSS styling from the main.css page-->
    <link rel="stylesheet" type="text/css" href="main.css">
</head>
<body>
    <main>


    </main>
</body>
</html>

=================================================

RANDOM CODE SNIPPETS

I don't know how to use this. I see that there's things like $query which I'm guessing is a variable because it has a $ in front of it, but does it need to be declared somewhere? How would I go about doing that? What kind of stuff needs to be attached to it?

$query = 'SELECT * FROM products
          WHERE categoryID = :category_id';
$statement = $db->prepare($query);
$statement->bindValue(':category_id', $category_id);
$statement->execute();

In the instructions it says that I need to Preparing, binding values and executing the query, but I have no clue what that means. Also we were told that part also needs to be in a try catch statement. As far as I can tell the one try catch thing I have in my code so far at least makes the "Database is Connected" string appear when I run the HTML code and click the submit button.

This is another code snipped I got from the powerpoint. Again I have no idea how most of it works. I know that foreach is a loop, but I don't know what the stuff in the parenthesis is. I guess it's aruments, but beyond that I don't understand what ________ as _______ is supposed to actually do. In Java I absolutely HATE for loops. I prefer to use do until or do while. For loops just don't seem to click very well for me. And somehow I'm supposed to use POST to get the data. I vaguely understand POST. In my understanding, it takes the name associated with an input box on the html form and whatever stuff is typed into it and stuffs it into a variable on the php page by putting the same name inside single quotes and setting it equal to a variable using POST. Ok, but beyond that I don't know what do do with that to make whatever the user types into the textbox actually work to retrieve data from a database and display it in a table.

<?php foreach ($products as $product) : ?>
<tr>
    <td><?php echo $product['productCode']; ?></td>
    <td><?php echo $product['productName']; ?></td>
    <td><?php echo $product['listPrice']; ?></td>
</tr>
<?php endforeach; ?>

Please go easy on me. It's my 4th week in this hellish class. So far the little bit of code I've managed to gather up has taken me about 14 hours thus far and I don't even know if any of it is right.

=============================================

Rather than actually show us how to code, the prof simply pulled up MS Paint and created pictures to show us what she wants this thing to look like. If I could post photos on here I would at the very least share them so people can have an idea of the goal.

The "Good Data" Picture she drew has a table kinds like this:

Zipcode data for Memphis, TN

=================================================

| 37501 | 35.12 | -89.97 | 15,517 |

| 38104 | 35.12 | -89.97 | 17,217 |

| 38016 | 35.12 | -89.97 | 10,799 |

| 38109 | 35.12 | -89.97 | 12,867 |

The error picture she drew looks kinda like this:

DATABASE ERROR

System message: SQLSTATE[HY000] [1045] Access Denied for user 'mgs_user@localhost' (using password: YES)

There was an error connecting to the Database.

Please try back later.

The input page:

And the HTML + CSS I posted shows how the input page should look. But I have no clue how to make stuff that a person types in to a textbox actually get data from a database and make it populate a table with the results. That's all we got, was premade instructions, premade powerpoints and MS Paint pictures. I've been trying to make sense of this with my textbook, but most of it seems to be written from the perspective that the reader already has a base understanding of what this stuff actually is. PDO is PHP Data Object, but beyond a textbook definition I don't know what that actually means. So many issues coming into play that's making this miserably difficult. I didn't even want to take a PHP course much less with this lady, but I had no choice.

Edited September 18, 2015 by Invidia

Invidia · September 18, 2015

From what I can tell, using the try catch it does say that the database is connected. So I think I managed to get that much working. But I have no clue how to actually make data show up based on what's put in the textboxes or where stuff needs to actually go. So confused.

mac_gyver · September 18, 2015

if you are not even sure that a $ starts a variable (programming is not about guessing or assuming, it is an exact science), you need to start with the basics. i would recommend that you read (so that you can internalize the information) at least the Getting Started and Language Reference (which covers variable naming) sections of the php.net documentation. php.net supplies the documentation in several forms, both on-line and down-loadable. i find the down-loadable .chm file particularly useful since it has a search-able index and in the cases where the index doesn't find what you want, you can use the search tab.

next, if you have a question to post on the forum, limit yourself to just the relevant verbiage. we can only give an answer when we know of a specific question. just telling us you don't know what something means isn't specific enough. you must tell us what exactly you don't understand, because we are not providing free tutoring services. we are providing direction and sharing programming knowledge.

Edited September 19, 2015 by mac_gyver

scootstah · September 19, 2015

Yikes. A lot to take in here. Let me attempt to pick out some of your problems and address them.

I don't know how to use this. I see that there's things like $query which I'm guessing is a variable because it has a $ in front of it, but does it need to be declared somewhere?

In that snippet, $query is being assigned to the value 'SELECT * FROM products WHERE categoryID = :category_id';

In the instructions it says that I need to Preparing, binding values and executing the query, but I have no clue what that means.

Prepared statements are a way to insert values into a query without the risk of SQL injection. SQL injection is a vulnerability which lets an attacker interrupt a query by entering specially crafted strings in user input. There is a ton of information readily available on SQL injection so I won't go into details. Basically, prepared statements mostly eliminate that vulnerability by binding the values internally in a safe manner.

The snippet you posted is an example of a prepared statement with bindings.

$query = 'SELECT * FROM products
          WHERE categoryID = :category_id';
$statement = $db->prepare($query);
$statement->bindValue(':category_id', $category_id);
$statement->execute();

Here, we are creating a named parameter in the query called :category_id which we will later use to bind the value to. The value gets bound in this line:

$statement->bindValue(':category_id', $category_id);

So, internally, the :category_id parameter is replaced with the value of $category_id.

Also we were told that part also needs to be in a try catch statement.

If configured to, PDO will throw exceptions when there are query errors, and errors in general. But, you did not tell it to do so. Right after you create a PDO connection you will want to add this line: $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Like this:

try {
    $db = new PDO($dsn, $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo '<p>You are connected to the database!</p>';
} catch (PDOException $e) {
    $error_message = $e->getMessage();
    echo "<p>An error occurred while connecting to
             the database: $error_message </p>";
exit();
}

Also, I noticed you are opening a PDO connection twice - you only need the one call, inside the try block.

I know that foreach is a loop, but I don't know what the stuff in the parenthesis is.

The stuff in the parenthesis is the array to loop over, and the value of the current array index. Take this example:

$fruits = array('apples', 'bananas', 'oranges');

foreach ($fruits as $fruit) {
    
}

foreach() will iterate over every item in $fruits. For each iteration, $fruit will be equal to the value of the current item. So on the first iteration $fruit is equal to apples, on the second iteration it is equal to bananas, and on the third and final iteration it is equal to oranges.

And somehow I'm supposed to use POST to get the data. I vaguely understand POST.

POST is an HTTP method that is used to send data from the client (browser) to the server (PHP application). When you make an HTML form, most times you are doing so with the POST method. When the browser sends a POST request, like after submitting a form for example, there is a request body that is populated with the form data. Its structure differs depending on the encoding type used, but it might look something like this: city=Portland&state=Oregon

When PHP sees a POST request has been made, it does some internal stuff and populates the $_POST superglobal. Basically, it deconstructs that string and creates an array based on the key/value pair. So in my example it would create an array that looks like this:

array(
    'city' => 'Portland',
    'state' => 'Oregon',
)

You can then access these values just like you would with any other array.

$_POST['city']; // equal to Portland
$_POST['state']; // equal to Oregon

You can then use these values to construct an SQL query (remember the named parameters earlier, with :category_id?) and select data from the database.

Hopefully that clears up some things for you. If you like, or if none of that made sense, feel free to chat with me externally. You can find me here:

AOL Instant Messenger: scootstah@gmail.com

Yahoo Instant Messenger: scootstah@ymail.com

Google Hangouts: scott@scottbouchard.me

Invidia · September 19, 2015

The reason I'm not totally certain those are variables is because I don't understand why there are two of them inside the parenthesis separated by the word as. I'm trying my best to make sense of it with what I have read. I've been trying to use codecademy and it seems that whatever is in the first spot gets stuffed inside whatever is inside the second spot. Like renaming it. The examples I went through on codecademy made it seem like you used a variable to make an array then then put that variable name in the first spot and use the word as then give it a name in the second spot to use. But in this case I'm not using an array that is initialized with a string or anything like that. I know that arrays start at position 0 in Java and other languages and I think it's the same in php. I'm VERY new to programming, but php in particular and being given virtually no instruction and deadlines with little clue as to how anything works is frustrating.

foreach is a loop. I got that. How is that supposed to work with a database? How am I supposed to make an array with a database? How do I connect it in? I have minimal understanding how POST works, so how does that get incorporated in from a textbox in order to get info from the db to put into an array?

I say "I think" because I'm new and not certain. I'm doing what I can to try to understand from what I've read and done on codecademy and watched on youtube and searched on php.net

I don't understand at all what the -> in the code snippet I posted is supposed to do. Bind Value from what I can tell seems to take an SQL column name and stick it to a variable. Where I'm getting lost at is that I'm not quite understanding how these things fit together or where they're supposed to go.

mac_gyver · September 19, 2015

in addition to needing a basic understanding of the php language before you try to use it to do anything, the biggest thing that helps with programming is to define what you are trying to do, before you try to write the code for it. trying to write code or modify existing code (which would also apply to code snippets you find or are given) without a clear definition of what end result you are trying to produce, just wastes a lot of time.

so, what are you trying to do, 1) a form with inputs for city/state, and 2) form processing code that takes the submitted city/state values, retrieves the matching information, if any, from the database table, and displays the results in a particular format.

you would then break down each of those main tasks into smaller and smaller steps, until you can finally write the code that accomplishes each step.

for the form processing code, those steps would be -

1) create a database connection, handling any connection errors, and as has been recommend, set the error mode to exceptions so that all the rest of the database statements will throw exceptions that you can catch and handle in one place.

2) check that a form was submitted - you were shown/given a method='post' form. however, this form is controlling what will be displayed. it should use method='get'. post method forms are for changing data on the server.

3) condition/filter/validate the submitted form data.

4) if there were no validation errors, use the submitted data in a database query.

4.1) form and run the sql statement for a SELECT query with a WHERE clause that will use the submitted city/state values, using place-holders for the input parameters.

4.2) prepare the query.

4.3) bind the input parameters to the place-holders in the sql statement.

4.4) execute the query.

5) retrieve the data that the query matched, if any. since your assignment mentions using a foreach() loop, the intent would be to fetch all the rows that the query matched into an array, then use the foreach() loop to loop over that array.

6) output the data, if any, in that particular format. if there is no matching data, output a message stating so.

this list of steps that you have defined will become comments in the code that you will write.

give me a couple of minutes and i may (no promises) post an example using pdo to do this.

hansford · September 19, 2015

The reason I'm not totally certain those are variables is because I don't understand why there are two of them inside the parenthesis separated by the word as.

You are looping through the array using an array expression. You can loop through the array a number of different ways, but foreach is convenient for many jobs.

$fruits = array('apples', 'bananas', 'oranges');

foreach ($fruits as $fruit) {  // $fruit initially points to the first element in the array
    
// after each loop is complete the array pointer is advanced to point to the next element in the array
}

I don't understand at all what the -> in the code snippet I posted is supposed to do.

This -> is the object operator. It is used to access properties and methods of an object instance.

class Foo
{
    private $name;

    public function __construct( $value )
    {
        $this->name = $value;
    }

    public function getName()
    {
        return $this->name;
    }
}

$foo = new Foo('MyFoo'); // create an object of class Foo and assign it to the variable $foo
echo $foo->getName();   // call a method of our object

mac_gyver · September 19, 2015

i'll try to answer some of the points from your last post above. sorry ahead of time for the bluntness that may ensue, because many of these things are basic/prerequisites that you should have, and may have, been shown before you were expected to use them -

The reason I'm not totally certain those are variables is because I don't understand why there are two of them inside the parenthesis separated by the word as.

that's the syntax for a foreach() loop. reading the php.net documentation up to the control structure section and then reading the specific page for a foreach() loop would have given you the information you need to understand what the syntax means. no guessing is required. programming languages are one of the most heavily documented subjects on the planet because it's impossible to use them without having a good language reference.

I've been trying to use codecademy

unfortunately, just being shown something and told what to type in a situation doesn't teach the meaning of it. that's mimicking/parroting. learning something technical like a programming (or a linguistic) language (which is not the same as learning how to program) is a process where you define a part of the language, then use it in an example to reinforce and internalize the meaning. you then build on what has been learned before. for example, if you haven't learned the general syntax for a php statement (what terminates a statement), learned the syntax for php strings, learned the syntax for php variables, learned the syntax for an assignment statement, and learned the definition for an echo statement, you won't know the actual meaning of every character in the following - $some_variable = 'hello'; echo $some_variable; that would let you generalize and use those same elements when writing your own code.

foreach is a loop. I got that. How is that supposed to work with a database?

since a foreach loop operates on an array of data (or something that's Traversable, like a PDOStatement object), you would need to get the result from the query into an array. the clearest way (that also separates the database statements from your presentation code :thumb-up: ), would be to use the pdo ->fetchAll() method. you can find information about the pdo class in the php.net documentation. since your code is dependent on the pdo class, reading just about all of the pdo documentation at php.net would be to your advantage.

I have minimal understanding how POST works, so how does that get incorporated in from a textbox in order to get info from the db to put into an array?

the data from a post method form will be available in the php $_POST array. the form field name='some_name' attributes determine the $_POST array index names - $_POST['some_name]. at this point, the submitted data is in a php variable that you can use any way you want in your code, such as binding it with a place-holder in a prepared sql query statement. this is real basic/fundamental php information that there are probably 5,000,000 examples posted on the web to find and examine.

I don't understand at all what the -> in the code snippet I posted is supposed to do. Bind Value from what I can tell seems to take an SQL column name and stick it to a variable.

again, the documentation for the php statements you are using defines what it all means. you don't have to guess, just make use of the documentation to learn the basics of the php language.

Where I'm getting lost at is that I'm not quite understanding how these things fit together or where they're supposed to go

that is the creative part of programming, which is different form learning the php language itself. see my post above about defining, then breaking the problem down into the steps that accomplish the task, to help with how to put statements together in a meaningful way.

Sign In

php with a database on xampp

Recommended Posts

Invidia

Link to comment

Share on other sites

Invidia

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

scootstah

Link to comment

Share on other sites

Invidia

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

hansford

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information