phppup

It seems that the auto increment for the ID prohibits use of an asterisk (to SELECT * and use everything EXCEPT the ID in the insert). Is there a lazy workaround (that's worthwhile)?

Ok, it seems to be working now (so I can build out on it). To be truthful, I had tried that (you taught me well) but your confirmation lead me to a different dumb mistake, as I was toying with this piece of code at the bottom of a PHP page and neglected to remove the line containing: mysqli_close($con) Therefore, there was no connection. Hence, failure. What sort of PHP could I use to alert me and prevent this type of issue. Note: I check the connection at the top of the page using if($con === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); } but it doesn't trigger (with the terminated connection in place) because $con is still valid. Thanks for the help.

I'm trying to copy a row so that I can later update within a given table. I'm not sure if there's a syntax error or rule error, or a missing piece but this code is not doing the job. $sql = "INSERT INTO $table (id, company_name) SELECT id, company_name FROM $table WHERE id = 26"; The table has auto increment ID, but the many alterations that I've made have failed. Guidance or solution, please. Thanks.

I've tried a few iterations but cannot get the correct syntax $sql = "UPDATE $table SET item=? WHERE id<10 id = LAST_INSERT_ID() ORDER BY id ASC LIMIT 1; SELECT LAST_INSERT_ID();"; //and then $last_id = mysqli_insert_id($conn); echo "Last inserted ID is: " . $last_id; Where is my mistake?

@mac_gyver I've gathered most of the "it can be done" from my web searches. I need an example (preferably in procedural PHP) of HOW to do it. I have made a few attempts, but none are producing results.

As a follow-up to my recent post to determining the AFFECTED ROWS after an update to a table, I am now interested in obtaining the row's ID. I previously obtained HOW MANY rows were affected. Now I want to know WHICH ROWS. In actuality, I'm using LIMIT 1 in my update, so I'm looking for a PHP echo of the row ID (so that I can refer back to it). I've seen some examples in SQL that involve a psuedo-INSERT, but they don't offer a PHP transition (procedurally) to accomplish an echo confirmation. Guidance or code solutions appreciated.

I couldn't agree more! But, @maxxd , are you encouraging me to post even more questions? LOL I've certainly learned and overcome PLENTY of obstacles here. But moving deeper into the pool may be a step further than I'm ready to "bind" before being fully "prepared" (see what I did there 🙂 ). TBH, I nearly fell overboard when mySQLi became mandatory But hey, ya never know. Thanks for the support.

@maxxd Thanks, I think I've got the prepared statement accomplished. I "understand" procedural. The "shorthand" of PDO gets me confused and lost. I have the same problem moving from vanilla JavaScript to jQuery. If you have a remedy (or therapy.. LOL) that will help me overcome these issues, please share. And please keep this confidential. Just between us. I don't want my personal information on the internet. *wink*

@Barand AWESOME, as usual. Thanks!! Can you give me a template for implementing the UPDATE $data = "Doe"; $sql = "UPDATE MyGuests SET lastname=$data WHERE lastname='Doe-Smith'"; if (mysqli_query($conn, $sql)) { echo "Record updated successfully"; with a prepared statement in procedural method, please.

The following code snippet will NOT provide what I'm interested in determining $sql = "UPDATE MyGuests SET lastname='Doe' WHERE lastname='Doe-Smith'"; if (mysqli_query($conn, $sql)) { echo "Record updated successfully"; } else { echo "Error updating record: " . mysqli_error($conn); } For argument sake, let's presume that Ms Doe-Smith's marital status has changed and she is returning to using her maiden name only. The records of her 5 children will also be effected (although not all used Mr. Smith's surname). The snippet's success/error message seems to fire based on the validity of $conn and $sql. If no records are updated (because there are no instances of Doe-Smith), the success message displays anyway because nothing went wrong with the process. How can I get a confirmation of whether an actual record was changed? Or how many? Or row numbers? (I've considered running a SELECT before the UPDATE, but thought that might create an unnecessary load factor.) Thanks for reading.

@Barand Yes, I knew that. *wink* I just thought I'd ask the chefs because I don't have the time to toy with the recipe. Nonetheless, your assistance has been greatly appreciated, and spot on, as usual. Thanks 🙂

@Phi11W To expand on your reply, I understand that NULL and "" are different entities, but would one be preferred as a value? My (limited) experience has been that strings stored in a table (name, address, etc) are relieved and displayed "as is" (more or less). But numbers (even if inaccurately stored as VARCHAR) can be used within computations to generate totals (new numbers). Question: If a row in a table visually has a value of 2 in field1, a 4 in field2, and field3 appears empty, how will it effect the result of a math equation with regard to whether field3 is actually "" versus NULL? Will field1 + field2 + field3 equal 6 in both instances? Or will NULL cause a recognizable error? What about multiplication? Is either "" or NULL defined as zero? (I'm guessing NO, but....) I appreciate everyone's help. Thanks.

@Barand Thanks for two genuinely educational and understandable responses. Did some more reading too. For clarification, do you technically need quantity INT NOT NULL DEFAULT 0; TYPE VARCHAR(10) NOT NULL DEFAULT "SUMMER"; Can the NOT NULL be omitted? Is there a reason to implement the (seeming) redundancy [since the default essentially auto-remedies the value from being null]?

@Barand Is there a benefit to adding a DEFAULT value (of empty) versus removing the NOT NULL parameter?

I have a table with 8 field titles and have successfully inserted data using a prepared statement targeting all 8 fields. I want to change my data collection strategy and INSERT into only 3 fields (where the other fields will populates via an UPDATE). This code gave me an error $sql = "INSERT INTO $table (id, first_name, company_name) VALUES (?,?,?)"; if($stmt = mysqli_prepare($link, $sql)){ mysqli_stmt_bind_param($stmt, "sss", $id, $first_name, $company_name); etc. starting that: Field 'field4' doesn't have a default value Am I obligated to provide a value for every field in the table, even if some are empty, or can I address ONLY the pertinent fields? Additionally, what is the "best" way to address the id field? Since it's an auto increment field and it seems like providing a false value is wrong. Yet a value appears to be required.

Great info, @gizmola. But perhaps I should rephrase. Staying with the same premise, I'm really ONLY interested in tracking the history of inventory (at this juncture). We only sell one type of truck and one type of car (vehicle history is the only focus for now). I'm going to track when we got the car and the wholesale price with shipping and fees, and, of course, sales data. All the same info is applicable to trucks EXCEPT that trucks are sold on consignment. So, I need to include a "sell by date" and, if not sold, a "date of return". Do I include these 2 columns in a single table, and only fill them with data when applicable? Or do I use one table for cars, and a separate table for trucks ( where the two tables are identical except for two columns?

In a hypothetical situation, suppose I want to record sales data for items in two broad categories: cars and trucks. There are obviously different makes and models within each category, but all have the same accessories (for the sake of discussion). However, trucks have a few accessories not available to cars, and vice versa. The inclusion of these accessories will reflect in the final sale price, which will be determined identically for both categories, and recorded along with basic info (date of order, date of pickup, payment method, etc.). What would be the correct/best database setup? One table that incurs some NULL entries of unavailable accessories? Two tables; one for trucks and one for cars? Something more? Reasoning? Pitfalls?

Yes @maxxd , I must have had a brain burp. I went for a walk and then it got me, "Duh, I'm getting what I needed." Now, i just need to implement it accordingly. Thanks @Barand and @kicken for your helping hands.

No, I don't @requinix I want someone that looks at the form to see the drop-down apparatus with the phrase "Car choices" (similar to a text field placeholder). it can be visible once the menu drops open, but should NOT be selectable (like an optgroup).

@kicken okay. I've got the "Trigger" alert popping up on any text field that I click. What is this indicating? Where to from here?

I thought of that, but am missing a connection. The radio selection kicks off a script that populates an empty <div> and displays a form. At that point, doesn't the form exist? Shouldn't the forms be accessible? This portion of scripting is AFTER and below all other code.

Same issue. For some reason, I cannot get a response when I try to access by the inputs or form ID. It seems pretty straightforward, so I wasn't sure if having JavaScript create the forms was creating an obstacle that I wasn't unaware of.

This is what I've done in the past, but it still allowed the NULL category to be a viable option (especially on a mobile device). I want something similar to an <optgroup> that is viewable prior to closing the drop-down.

I have a lot of working code that is essentially doing this: <div id="mydiv"> radio buttons </div> <div id="div2"> form populates here </div> <script> //listen for a radio to be clicked and then display the appropriate form in div2 if(radio1){ document.getElementById("div2").innerHTML = blah blah blah if(radio2){ document.getElementById("div2").innerHTML = blah blah blah etc. Now I want to listen for a change in any input on the form to trigger a response. However, I am having difficult accessing the form inputs. document.getElementById("name").addEventListener("click", displayDate); function displayDate() { alert('Hurray'); } //does not work and essentially disables the JavaScript that follows I managed to gain success with document.getElementById("div2").addEventListener("click", displayDate); But this triggers the associated function with any click at any location in the div. How can I "drill down" to the specific inputs inside the form?

This will give me a dropdown menu <select name="cars" id="cars"> <option value="volvo">Volvo</option> <option value="saab">Saab</option> <option value="mercedes">Mercedes</option> <option value="audi">Audi</option> </select> and by default, the first choice, Volvo, will be visible to the user. This will let me categorize the menu for the user <select name="cars" id="cars"> <option value="" >Car choices</option> <option value="volvo">Volvo</option> <option value="saab">Saab</option> </select> but the user CAN select the "Car choices" as an option. Using an <optgroup> tag gets me CLOSER, but it is NOT visible until after the user clicks the drop-down. How can I create a visible category title that is not selectable in my drop-down?