Jim R Posted March 27, 2020 Share Posted March 27, 2020 Looking to INSERT a row in a data table and keeping the User on the current page. Will it work like what I have below, or do I need to send it to its own page then return the User back to where he started? What I have below isn't inserting a row. No errors showing. function bookmark_add () { if (isset($_POST['submit'])) { include("/home2/csi/public_html/resources/con.php"); $query = "INSERT INTO a_player_bookmark (bookmark) VALUES ('1')"; $results = mysqli_query($con,$query); echo mysqli_error($con); } $output = '<form name="bookmark_add" method="post" action="" class="bookmark-plus">'; $output .= '<span><input type="submit" name="Bookmark"></span>'; $output .= '</form>'; return $output; } Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 27, 2020 Share Posted March 27, 2020 YOu are inserting a row into a table that has a single column that contains a 1. That's it? A completely unrecognizable piece of data? As for remaining on the page. Well - an ajax call would do this for you but are you ready for that? The alternative would be to submit the form to your same script that produced the page, do the logic to update the db and then send back the same page that you had with all the data items in their approriate fields again. Do you know how to re-display a form with the input data that use put into it before submitting it? Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 27, 2020 Author Share Posted March 27, 2020 Ultimately I will be adding three columns: itemID, userID, username (so I can easily look at it on my phone). I'm creating a bookmark for an item for each User. I've not messed with AJAX much. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 27, 2020 Share Posted March 27, 2020 If you have no knowledge of Ajax I'd recommend the easy way. Make sure you have php vars setup in your html input tags. That way when your script grabs the data from there those values can be sent back out once the script is done and the screen will look pretty much the same, although I would imagine you would add some kind of message saying success or failure. PS - I think you may have a table design problem. You are probably going to record these bookmarks for an item, hence the itemid (don't use mixed-case!). But don't store the username on the same table as these records. Create a users table with a userid and a username and whatever else you want. Then create a bookmarks table with the itemid and the bookmark value and the userid only. That's how a true RDBMS is designed. Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 27, 2020 Author Share Posted March 27, 2020 (edited) 17 minutes ago, ginerjm said: If you have no knowledge of Ajax I'd recommend the easy way. Make sure you have php vars setup in your html input tags. That way when your script grabs the data from there those values can be sent back out once the script is done and the screen will look pretty much the same, although I would imagine you would add some kind of message saying success or failure. PS - I think you may have a table design problem. You are probably going to record these bookmarks for an item, hence the itemid (don't use mixed-case!). But don't store the username on the same table as these records. Create a users table with a userid and a username and whatever else you want. Then create a bookmarks table with the itemid and the bookmark value and the userid only. That's how a true RDBMS is designed. (I'm told all the time not put full name data in multiple tables. I get it. I'm slowly drifting more in that direction. Problem is, I often need to quickly get to data in my phone, and setting queries with joins isn't always feasible, and I also have a couple of people who can view my database who zero idea how to use it other than like a spreadsheet. I'm not dealing with big data. That's why I added the explanation in ( ), to let you know why I was doing that.) The form is only going to have the Submit button, which I plan on using a image for. I just to make sure for now I can add the row and keep the User at the same spot. They're going to have this option wherever the Item is listed (about four different spots). So you think I should send it to it's own file to process, then back? I've done a lot with forms that get sent to a file to execute a database query, then moved the User back or to the different page. I just thought it could all in the same place. Edited March 27, 2020 by Jim R Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 27, 2020 Share Posted March 27, 2020 (edited) I would write a script that starts by testing if the form is submitted. If it has been submitted I process the inputs setting php vars with their values. Once done, I can now send back the html page with or without the data values that I extracted from $_POST along with any other info. I keep the output of the html code at the end of my script and just fall into it when ready. (Actually I bury my html page code all in a php function that does my entire output of the html to make it easy to isolate. Any dynamic html code (an html table of results say) I pass into it with an argument in the function header and place the arg where it fits in the body of the html.) Edited March 27, 2020 by ginerjm Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 27, 2020 Author Share Posted March 27, 2020 16 minutes ago, ginerjm said: I would write a script that starts by testing if the form is submitted. That's what I was doing, and it didn't work. I'm not sure if it's not working because of the query or if it's because it just won't work as I have it set up. Right now I have this all in a Function in a functions files, then referring to it on my main page. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 27, 2020 Share Posted March 27, 2020 I can't imagine what your code looks like. Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 27, 2020 Author Share Posted March 27, 2020 It's just that above, which then feeds into... function player_name ($nameFirst,$nameLast) { echo '<a href="/tag/'. strtolower($nameFirst) . '-' . strtolower($nameLast) .'">'. $nameFirst . ' ' . $nameLast .'</a> '; echo '<span>' . bookmark_add() . '</span>'; } Once I get it done, it won't be bookmark_add() in there. It will just be bookmark_choose(). I'll have to determine if the bookmark is chosen or not. Right now, I'm just trying to see why the query isn't working. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 27, 2020 Share Posted March 27, 2020 I still don't see any php code. Getting bored. Good bye Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 27, 2020 Author Share Posted March 27, 2020 It was all PHP code. Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 27, 2020 Author Share Posted March 27, 2020 Just dawned on me (isset($_POST['submit'])) needs to be the name of the button, not the type. It otherwise works. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 27, 2020 Share Posted March 27, 2020 It is not really php code. It is echo's of html code and no real logic - none of which fits your topic title. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 27, 2020 Share Posted March 27, 2020 (edited) 3 hours ago, Jim R said: and I also have a couple of people who can view my database who zero idea how to use it other than like a spreadsheet That is no excuse to design db tables like spreadsheets. You can always create views for the technically challenged users. EDIT: For example there is a "fixture" table in the db in the tutorial on my site mysql> select * from fixture; +---------+----------+----------+-----------+-----------+--------+ | idmatch | hometeam | awayteam | homegoals | awaygoals | weekno | +---------+----------+----------+-----------+-----------+--------+ | 1 | 4 | 2 | 1 | 0 | 1 | | 2 | 2 | 4 | 2 | 2 | 2 | | 3 | 3 | 2 | 4 | 4 | 3 | | 4 | 1 | 3 | 1 | 1 | 1 | | 5 | 2 | 3 | 1 | 2 | 4 | | 6 | 3 | 1 | 1 | 3 | 2 | | 7 | 4 | 3 | 2 | 0 | 5 | | 8 | 2 | 1 | 0 | 3 | 5 | | 9 | 1 | 4 | 2 | 4 | 3 | | 10 | 4 | 1 | 4 | 4 | 4 | | 11 | 1 | 2 | 4 | 1 | 6 | | 12 | 3 | 4 | 1 | 4 | 6 | +---------+----------+----------+-----------+-----------+--------+ But to make it a bit friendlier, setting up a view gives mysql> select * from fixture_view; +---------+--------+----------+-----------+-----------+----------+ | idmatch | weekno | hometeam | homegoals | awaygoals | awayteam | +---------+--------+----------+-----------+-----------+----------+ | 4 | 1 | Laker | 1 | 1 | Jardine | | 1 | 1 | Cowdrey | 1 | 0 | Grace | | 6 | 2 | Jardine | 1 | 3 | Laker | | 2 | 2 | Grace | 2 | 2 | Cowdrey | | 9 | 3 | Laker | 2 | 4 | Cowdrey | | 3 | 3 | Jardine | 4 | 4 | Grace | | 10 | 4 | Cowdrey | 4 | 4 | Laker | | 5 | 4 | Grace | 1 | 2 | Jardine | | 7 | 5 | Cowdrey | 2 | 0 | Jardine | | 8 | 5 | Grace | 0 | 3 | Laker | | 12 | 6 | Jardine | 1 | 4 | Cowdrey | | 11 | 6 | Laker | 4 | 1 | Grace | +---------+--------+----------+-----------+-----------+----------+ Edited March 27, 2020 by Barand 1 Quote Link to comment Share on other sites More sharing options...
kicken Posted March 27, 2020 Share Posted March 27, 2020 There's two parts to this, so solve one then the other rather than doing both at the same time. First problem is saving your data. Create a function that you can call with your data and it will save it to your database. Use parameters for the function rather than accessing $_POST directly. For example: function bookmark_add($userId, $itemId){ //run INSERT statement } While getting that working, just use a normal form that will submit the data and refresh the page, eg: <?php if ($_SERVER['REQUEST_METHOD']==='POST'){ //Call your function with the data bookmark_add($_POST['userId'], $_POST['itemId']); } ?> <form method="page.php" action=""> <p>ItemID: <input type="text" name="itemId"></p> <p>UserID: <input type="text" name="userId"></p> <p><button type="submit">Save</button></p> </form> Once you have your function working exactly how you want it, then move on to your "without reloading the page problem." To do this without reloading the page, you need to use javascript to submit the values in the background using AJAX/XHR. Grab something like jQuery and use it's .post method to make this super simple. Now you create a javascript function which will gather your form data and submit it to a PHP page which then calls your PHP function to save it. For example: function submitBookmark(){ var postData = { userId: $('#userId').val() , //.... }; $.post('page.php', postData) .then(/* Success handler */) .catch(/* Failure handler */) ; } Finally setup that function to be executed whenever the user clicks your + image. 1 Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 28, 2020 Author Share Posted March 28, 2020 (edited) 13 hours ago, kicken said: I got my code to work without an error, and I thought I would be able to expand on that by Joining my a_players table so I can match the player's ID. Figured out the syntax of it (I think), but instead of just inserting one row for the playerID, it inserts the playerID for every possible row. In this case 64 rows. So I get 64 rows of bookmark = 1 playerID = 251 (for example) - hidden data if (isset($_POST['bookmark'])) { $pid = $_POST['pid']; include("/home2/csi/public_html/resources/con.php"); $query = "INSERT INTO a_player_bookmark (playerID,bookmark) SELECT p.id, 1 FROM a_players p WHERE p.id = '" . $pid . "' "; $results = mysqli_query($con,$query); echo mysqli_error($con); Edited March 28, 2020 by Jim R Quote Link to comment Share on other sites More sharing options...
Barand Posted March 28, 2020 Share Posted March 28, 2020 13 minutes ago, Jim R said: SELECT p.id, 1 FROM a_players p WHERE p.id = '" . $pid . "' Seriously? How many records in a_players have that id? Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 28, 2020 Author Share Posted March 28, 2020 14 minutes ago, Barand said: Seriously? How many records in a_players have that id? Just one, and not even any of the ones on that particular query. So my understanding of what I have is the SELECT instances are the values for playerID and bookmark. If not, then I'm mistaken on what that query is doing. I Googled php, mysql, insert, join. Found about six or seven resolved questions on a couple of different places. They all had that syntax, without any explicit VALUES terms, so I assumed what was in that SELECT were the values being inserted. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 28, 2020 Share Posted March 28, 2020 16 minutes ago, Jim R said: Just one Then don't you think that query is a waste of space? Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 28, 2020 Author Share Posted March 28, 2020 7 minutes ago, Barand said: Then don't you think that query is a waste of space? No, I'm trying to insert one row of data for each submit, allowing the User to bookmark a player to follow. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 28, 2020 Share Posted March 28, 2020 But you know the player id, so why do you need a query to tell you what the player id is where the player id = X Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 28, 2020 Author Share Posted March 28, 2020 10 minutes ago, Barand said: But you know the player id, so why do you need a query to tell you what the player id is where the player id = X I tried it without the join and got the same result. It posted the correct playerID and bookmark = 1...64 rows. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 28, 2020 Share Posted March 28, 2020 Have you run the code 64 times, perhaps? And what is this "join" you keep mentioning? Quote Link to comment Share on other sites More sharing options...
Jim R Posted March 28, 2020 Author Share Posted March 28, 2020 $query = "INSERT INTO a_player_bookmark (playerID,bookmark) VALUES ('".$pid."', 1) "; It's producing correct values for 64 rows. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 28, 2020 Share Posted March 28, 2020 That query will insert 1 row. If it's inserting more than that then you are running that code more than once. Is it inside a loop that isn't shown? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.