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.