Hi folks, to start with my skill level is a 1/10 but hoping to improve this hobby.
I have a database in SQL very simple
myShop.FOOD
ID | ITEM | PRICE | SIZE
1 | Chicken | 2.00 | 1 Piece
(i think all beginners have done something like this lol good old shopping/video stores)
I want a PHP page which will display this table and either let me edit/delete/add in the table or even have a form below which i can do this.
I have a page in its most basic form with no bootstrap etc as yet see code below. I am not looking for the “answer” so to speak but if i am missing say three parts, as to what they are and why in the hope to learn.
TIA - Lee
And no these credentials are not ‘real’
<?php
$con=mysqli_connect("localhost","Admin","Password","myShop");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM FOOD");
?>
<table id="FOOD">
<thead>
<tr>
<th>ID</th>
<th>ITEM</th>
<th>COST</th>
<th>SIZE</th>
<th>Edit/Delete</th>
</tr>
</thead>
<tbody>
<?php
while($row = mysqli_fetch_array($result))
{
echo "<tr>" ;
echo "<td>" . $row['ID'] . "</td>";
echo "<td>" . $row['ITEM'] . "</td>";
echo "<td>" . $row['COST'] . "</td>";
echo "<td>" . $row['SIZE'] . "</td>";
echo "<td>" . $row['Location'] . "</td>";
echo "<td>Edit Hyperlink / Delete Hyperlink</td>"; <!-- i need the delete from SQL command here -->
echo "</tr>";
}
?>
</tbody>
</table>
<br><br><br>
<form>
<table>
<tr>
<td>
ITEM : <input type="text" name="ITEM"> <!-- i know i need the code here to edit or add to the table -->
</td>
</tr>
<tr>
<td>
COST : <input type="text" name="COST">
</td>
</tr>
<tr>
<td>
SIZE : <input type="text" name="SIZE">
</td>
</tr>
</table>
<input type="submit"> <!-- edit or add option -->
</form>
The database table should be named item(s)/product(s) or similar. Food is a specific category of an item. If you need to organize items by their category, have a category table, then store the category_id in the items table.
Store the connection code in a separate .php file and ‘require’ it when needed. This will eliminate all question about posting credentials, since you would not be posting that code when asking about the remainder of the application.
Use the much simpler and more consistent PDO extension. The prepared query interface of the mysqli extension is overly complicated and completely different from the non-prepared query interface, requiring you to learn and use two different sets of statements. The PDO extension allows you to operate on the result from both a prepared and non-prepared query in the same way, and the PDO extension requires less statements to accomplish tasks. An added advantage of the PDO extension is that you can use the same php statements for about 12 different database types (the actual sql may have differences), so you only have to learn one set of statements, instead of learning a different set for each different database type.
Don’t unconditionally output the raw database statement errors onto a web page. Yes, when learning, developing, and debugging code/queries you should output all database statement errors. However, on a live/public server, you should log all database statement errors. The way to do this is to use exceptions for database statement errors - connection, query, prepare, and execute, and in most cases let php catch the exception, where it will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) This will let you remove any existing error handling logic, simplifying the code, and give you error handling where you don’t have any now.
You should form the sql query statement in a php variable, then use that variable in the query statement. This will let you echo the sql statement for debugging purposes, it will separate the sql query-build syntax from the php code executing the query, reducing typo errors, and it will let you change the database extension without needing to touch the sql syntax. You should also fetch all the data from a query into an appropriately named php variable, then test/use this variable in the rest of the code. This will make debugging easier and allow you to change the data-source without needing to touch the presentation logic. Most SELECT queries should have an ORDER BY term so that the data will be in a known order.
The actual delete action should use a post method form. You could have a combined edit/delete link that results in a page with a post method form that would have separate edit and delete submit buttons.
The distinction between editing existing data or adding new data is the existence of an id. You can use one common form for both, with the edit form having a hidden field containing the id.
When editing data, there would be a $_GET[‘id’] parameter (from the combined edit/delete link), that you would use to retrieve the initial data to populate the form field values with. However, after the form has been submitted, you would want to use the submitted values to populate the form field values, in the case where a validation error occurred. To accomplish this, use a common internal variable to hold a working copy of the data. You would initialize the variable to an empty array. Inside the form processing code, you would trim and copy the $_POST data to this variable. After the end of the form processing code, if this variable is empty and you are editing existing data, you would query for the initial data and store it into the same common internal variable. You would use this internal variable in the rest of the code. Any dynamic values should have htmlentities() applied to them when you output them onto a web page.
I remember back in the MS Access day where you could link tables by common keys etc and was very simple to do. I have not reached this level of skill yet but do understand the concept.
I in the final product would want a table for [Beers] [Wines] [Meat] [Snacks] [Frozen] [Other] so my use of [Food] was a lazy example on my part. Each of these tables have ID|ITEM|COST|SIZE I just (as of this moment) do not know how to link these together. But duely noted.
I have found in google how to do this based on your advice and have change to require and moved the valuable information out of the main page - Thank you
Very interesting and incredible good point especially for a novice. So before i go of and google this, am i right in understanding that PDO will use my standard SQL statement as well as many variants and run the query as if i was typing the syntax in MySQL. This would be helpful as I can fine tune the statement in MySQL until it is correct and then copy what i need straight in to PHP with a variable.
Understood, and this is not ever seeing the light of day. I was looking to order an online shop with a local farm because of this COVID and thought "this is better idea that doing a video rental shop as a learning project
Can you give me an example please
How would this happen? would you overwrite the ID? I am looking at the POST as the literal sense of the word, you would be POSTING something to the DB so not sure how you would delete by post something more into the DB
No. Use ONE single table to hold all the item information. By using a separate table for each type of item, you are creating a database management mess.
So for example if there were a 1000 items, I didn’t think it was a good idea to have that in a huge list (im talking in the future and being educated further) having a 1000 items in a table in a webpage for an end user to trawl through wouldnt that be … a mess in itself? I think as an end user I would like to have a tabbed experience and say select just BEERS and administer just the BEER.
In writing, a tiny spark hit my brain which is i suppose the purpose in getting us to think about things, have a fifth column being …| TYPE and run an SQL query in a tabbed environment to pull off * FROM Products WHERE TYPE = “BEERS”
The PDO extension or the mysqli extension are just the means by which your php code interfaces with the database server. They send any sql query statement and prepared query data to the database server and allow you to access the result that the database server sends back.
With today’s server hardware, ‘large’ starts at about 5 million rows of data in a single table.
A visitor to a site would limit the displayed product information by using filters, such as a wild-card search, picking a category, a sub-category, a price range, a size, a manufacture, a model… If there are a lot of items for any filtered result, you would use pagination to display the results.
$sql = "SELECT id, name, cost, size FROM items WHERE category_id = ? ORDER BY name"; // your ITEM column is probably the item name and should be named as such
// you should also list out the columns you are SELECTing as this helps to document what you are doing and makes sure that you are only selecting the data that you want
// and since we are now using the PDO extension
$stmt = $pdo->prepare($sql); // prepare the query
$stmt->execute([$category_id]); // execute the query, supplying the category_id from wherever it has been input to the code
$item_result = $stmt->fetchAll(); // fetch all the rows of data that the query matched
// test/use $item_result in the rest of the code
Data is almost never actually deleted. If someone took the time to create the data in the first place, it has some value and may need to be ‘recoverable’ if a mistake was made in deleting it. A database table would have a ‘deleted’ column, that has a default false value. To delete data, you would UPDATE this column to a true value. To simplify the normal queries operating on this data, you create a ‘view’ that only includes data that has not been deleted.
A POST method form is used when performing an action on the server, such as creating, updating, or deleting data, sending an email, uploading a file, … The data submitted to the server side scripting language. It is up to the code to decide what to do with that data.
Yes and no. Remove the try/catch logic, for two reasons. The first I have already stated. Let php catch and handle most exceptions so that any database errors will get displayed or logged the same as php errors. The second is wherever you found that, it is pointless. It is re-throwing an exception with the same values it just caught. The only case where you should have try/catch logic for database statement errors is if your code can do something about the error that occurred. The only common case is when inserting/updating duplicate or out of range user submitted data. In this case, your code should catch the exception, detect if the error number is for something that your code can handle, then setup a message telling the user exactly what was wrong with the data that they submitted.
Where my thoughts are now is how i would ‘edit’ an item.
To delete i would add a hyperlink button on the end of the row array which will change the ‘live’ status from 1 to 0
To add I would use the form fields below the table to add a new item
But as i said ‘edit’ hmmmm… I do not want the user to edit the ID number as this is an auto increment number and a unique identifier and a user editing this would surely create foreseeable problems. So if i use the form fields below the table im not sure how i would get the particular record into the form to edit.
No. Use a post method form as already stated. Beside being a convention/standard, a search engine spider will only make GET requests. If you cause an action to occur due to a link on a live/public web page, every time the web site gets indexed by a search engine it will follow all of those links and at a minimum consume unnecessary resources, but at the worst alter all the data.
For editing (vs creating a new entry) -
If there’s a non-empty $_GET[‘id’] parameter, you would output a hidden form field, with name=‘id’, with that value in it, if not, don’t include the hidden field in the form. In the POST method form processing code, if there’s a non-empty $_POST[‘id’], you would execute the UPDATE query logic, if not, execute the INSERT query logic.
