Jump to content

CyberShot

Members
  • Content Count

    320
  • Joined

  • Last visited

Community Reputation

0 Neutral

About CyberShot

  • Rank
    Advanced Member

Profile Information

  • Gender
    Not Telling
  1. CyberShot

    how to select two tables in update query

    Ok, I have remade my table to match what you have posted.I had to alter the tables in order to set up the foreign keys. Was that necessary? ALTER TABLE unit ENGINE=InnoDB; ALTER TABLE payment ENGINE=InnoDB; I was then able to set up the relationships. I set the payment_id a the primary_key and the unit_id as the foreign key in the payment table. The unit_id is the unit table should be the primary key set to auto_increment. Is this good?
  2. CyberShot

    how to select two tables in update query

    for the unit table. I set the unit_id as the primary key and auto increment and for the payment set the unit_id as primary key auto increment?
  3. CyberShot

    how to select two tables in update query

    I agree with you 100%. I have managed to create an html select that does display each unit from the database where the value of the select is the address table primary key. This is a home owners association and what I am trying to keep tack of is the HOA dues. Those dues pay the water, sewer, garbage, landscaping and electrical for the complex. There are 14 units. Each unit pays monthly. There is no good cheap software out there that does what I want so I was trying to make something myself but as you can tell, I don't know what I am doing. I made the database, inserted some records and have managed to display them in a table. The html form was at this point was just to get a working query that I could work off of and then make more correct with prepared statements at a later time. Once the basic structure was up and running. The update queries are not working. I can get it to tell me that the database has been updated but the new record doesn't actually show up. Maybe I should just scrap the project and go back to watching TV and playing games.
  4. CyberShot

    how to select two tables in update query

    I only need one record per month per unit. each unit could actually make multiple payments per month though. My plan is to output each month into a table and each month the data in that table would reset to zero to show the new payments for each unit for the current month. Then the database would hold all payments made over time. I would like to query the database to show units that have not paid, how much they owe and when the last payment was made and what months are missing. You said it's unusual to join the primary key of one table to another. I guess I don't understand because that is how I thought it was done. What is the right way to do it as that is the way I would like it to be.
  5. CyberShot

    how to select two tables in update query

    here are my two tables. you will see in the below in the amount table, there is one record that has a duesID of 5 which does correspond to the address table unitID of 5. This is the correct record to show for unit 4109. I am trying to get a query that will choose the unitID and put the proper record down below so that when I query the database, the correct record will show for the correct unit. Right now, I have a form with unit #, date, amount paid and notes. My thought was that I could put in the unit number and then have the query select the matching number from the unitID in the address table and then insert the record into the amount table with the matching duesID number so that when I want to select a single record, I could just choose the unit number which will match the unitID and that record will populate. mysql> select * from address; +--------+----------------+ | unitID | unit | +--------+----------------+ | 1 | 4101 | | 2 | 4103 | | 3 | 4105 | | 4 | 4107 | | 5 | 4109 | | 6 | 4111 | | 7 | 4113 | | 8 | 4115 | | 10 | 4119 | | 11 | 4121 | | 12 | 4123 | | 13 | 4125 | | 14 | 4127 | | 9 | 4117 | +--------+----------------+ mysql> select * from amount; +--------+------+------+------+---------------------+------------+---------------------+ | duesID | dues | late | paid | notes | date | autodate | +--------+------+------+------+---------------------+------------+---------------------+ | 5 | 210 | 0 | 1 | paid through paypal | 2018-12-04 | 2018-12-10 20:54:07 | +--------+------+------+------+---------------------+------------+---------------------+
  6. CyberShot

    how to select two tables in update query

    Barand. I am trying to get your query working but I am having trouble. It is close though but I am getting an error at the last line. Here is what my database looks like table address unit ( varchar(20) ) unitID ( int, primary key, auto increment ) table amount autodate (timestamp) dues (int(6) ) late (varchar(1) ) paid (varchar(1) ) notes ( varchar(500) ) duesID ( int, primary key, auto increment ) Right now, I am just trying to get a test query in so that I can get a "scaffold" to work off of. so I thought the way this would work is that the query would check the unitID in the address table to see if it matches the "unit" number passed through the post variable from the html form. Benanamen said not to create useless variables, so I took them out and just used the post variables as suggested. So my html form has a text box for unit #, date, amount paid and notes Right now, I am just trying to get the unit # to match the primary key in the address table and then put the amount, and notes into the amount table. After I get that working, I will work off of that to get the rest of the fields to populate. I have 14 entries in the address table and 1 in the amount table. As the query is now, it's says I have an error in my sql syntax "WHERE ad.unitID = '10' The ten should match the primary key in the address table and put the record into the amount table to match. Here is what I have been trying with my query $sql = "UPDATE amount am INNER JOIN address ad ON ad.unitID = '{$unit}' SET dues = {$amount}, notes = '{$notes}', WHERE ad.unitID = {$unit}";
  7. CyberShot

    how to select two tables in update query

    all of this is done on localhost. I would assume it's not as dangerous? I have updated the code to use the if($_SERVER['REQUEST_METHOD'] == 'POST') How do you perform an update that doesn't have variables? are you saying that they are ok to pass if I use prepared statements but not just in an mysqli->query? I do remember reading that PHP_SELF was vulnerable attacks. What should I put there then? I don't want to separate my update function out of the db class. What is the best method for this? why can't you output internal system errors? how do I get my errors if I don't output them?
  8. I created an htlm form using $_SERVER['PHP_SELF'] to update my database and I am calling the form like so <?php if( $_POST['update']){ $unit = $_POST['unit']; $date = $_POST['date']; $amount = $_POST['amount']; $notes = $_POST['notes']; $york->update($unit, $date, $amount, $notes); } ?> in my database class, I have created an update function public function update($unit, $date, $amount, $notes){ $conn = $this->link; $sql = "UPDATE amount SET dues = ' . $amount . ', notes = '. $notes .' WHERE duesID = '. $unit .'"; if (mysqli_query($conn, $sql)) { echo "Record updated successfully"; } else { echo "Error updating record: " . mysqli_error($conn); } } $this->link connects to the database in my __construct and there are no errors as far as that goes. When I hit the update button in the html form, the page reloads and there is a message saying "Record updated successfully" but when I check the database, there isn't any new record. Looking at it again, I have realized that the query is looking for duesID to match the $unit number which in this case should be 10 but the amount table doesn't have a record for that. so what I need is a query that will select the address table and amount table then update the amount table where the address table unitID = $unit which is passed through my html form. How do I do that? The Address table does have a 14 entries in it with just to columns. Unit and unitID. Is this where a join comes into play?
  9. CyberShot

    fatal error: call to a member function

    this is true that the error does give you the line number but it isn't true that the line it points out is always the problem. It could be 2 or 3 lines above the actual line pointed out. I have seen this before. That said, I do not have any formal programming schooling. I am trying to teach myself which is not as easy as it sounds. There are lots of tutorials out there but they are notorious for leaving out information. They do things but do not tell you why they did them. I just finally came to understand the use of "$this->" as of yesterday.
  10. CyberShot

    fatal error: call to a member function

    I see in my post that the braces are messed up. I just didn't copy the code correctly. They are not messed up in the class
  11. I am trying to create a class and I ran into a problem. The connect function is returning a fatal error on the line below and I can't figure out the issue. $result = $this->conn->query("SELECT unit FROM address"); class database { private $server = "localhost"; private $db_user = "my user"; private $db_pass = "my pass"; private $db_name = "my database"; private $conn; private $result = array(); function connect(){ // Create connection $this->conn = new mysqli($this->server, $this->db_user, $this->db_pass, $this->db_name); if ($this->conn->connect_error) { echo "Not connected, error: " . $mysqli_connection->connect_error; } else { //echo "Connected."; return $this->conn; } public function displayHeader(){ $result = $this->conn->query("SELECT unit FROM address"); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { echo '<li>' . $row["unit"] . '</li>'; } } else { echo "0 results"; } $conn->close(); } } }
  12. CyberShot

    unserialize trouble

    when I first get the information out of WordPress, it looks like this ( this is just a short bit of it ) $theID = get_post_meta(get_the_ID()); $string = $theID['panels_data']; var_dump($string); //gives me the below line string(748) "a:3:{s:7:"widgets";a:1:{i:0;a:3:{s:5:"title";s:4:"home"; When I pass the string to unserialize function, I get an error $string = unserialize($string); echo $string; "warning" unserialize() expects parameter 1 to be string, array given in
  13. CyberShot

    unserialize trouble

    That helps a little but my main problem still exists. I get my data and when I dump the variable, it says it's a string that looks like this a:3:{i:1;s:6:"elem 1";i:2;s:6:"elem 2";i:3;s:7:" elem 3" yet when I pass that variable into the unserialize function, I get an error. I thought this is what unserialize was for. it says unserialize() expects parameter 1 to be string, array given in This is why I am confused.
  14. CyberShot

    unserialize trouble

    I have what I think is a serialized information in my WordPress site.. Here is a partial chunk a:3:{s:7:"widgets";a:1:{i:0;a:3:{s:5:"title";s:4 I thought that if I use unserialize, it would put it into an array or something. I thought unserialize takes a string which according to the data type looks like what I would be passing after going through my foreach loop but when I try to unserialize it, it tells me i am trying to convert an array to a string. Not sure what I am doing wrong here. foreach ( $string as $option) { echo unserialize($option) ; }
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.