Why would a counter fail to advance
Results 1 to 14 of 14

Thread: Why would a counter fail to advance

  1. #1
    Senior Member
    Join Date
    Sep 2012
    Posts
    269

    Exclamation Why would a counter fail to advance

    This should be a very simple script, but something is terribly wrong, and I don't know what.

    PHP Code:
    <?php


        
    $host
    ="localhost";
    $user="user";
    $password="pass";
    $Dbname "Food";

    $cxn mysql_connect($host,$user,$password)
    or die (
    mysql_error() . "couldn’t connect to server");


    mysql_select_db($Dbname$cxn)
    or die(
    mysql_error() . "couldn't select database");

    $RecipeTitle addslashes($_POST['RecipeTitle']);

    mysql_query("INSERT INTO `Recipes` (`RecipeTitle`) VALUES ('$RecipeTitle')")
    or die(
    mysql_error() . "  You dope, this does not work");

    for (
    $x=1$x<=20$x++)
        {
    echo 
    $x '<br />';

            
    $Qty 'q' $x;
            
    $Mea 'm' $x;
            
    $Ing 'i' $x;
            
    $Quantity $addslashes($_POST['$Qty']);
            
    $Measure $addslashes($_POST['$Mea']);
            
    $Ingredient $addslashes($_POST['$Ing']);
            
    echo 
    $x ' ' $Qty ' ' $Quantity;
            
            
    mysql_query("UPDATE `Recipes` SET `$Qty` = '$Quantity', `$Mea` = '$Measure', `$Ing` = '$Ingredient' WHERE RecipeTitle = '$RecipeTitle'"
                or die(
    mysql_error() . "  Update failed");

            
            echo 
    $Quantity .  '  ' $Measure .  '  ' $Ingredient '<br />'
        }
    ?>
    The first 'echo' inside the 'for' loop returns a '1', and there the script dies. The second and third echoes returns nothing at all, and the update query doesn't update anything. I have to be overlooking something that's painfully obvious.
    Last edited by bradgrafelman; 01-27-2013 at 05:33 PM.

  2. #2
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    the mysql_*() functions are deprecated. don't use them!

    addslashes is even worse.

    ---------------
    If you'll enable error reporting, you'll get something like the following:
    Notice: Undefined variable: addslashes in ...
    Fatal error: Function name must be a string in ...
    You're typing $addslashes() where you meant to type addslashes().

    ----------------
    As for your UPDATEs, what exactly are you trying to do?
    Your `Recipes` table has twenty different `q*`,`m*` and `i*` fields, which all hold the same values?
    Last edited by traq; 01-27-2013 at 03:58 PM.

  3. #3
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    I said the solution would be painfully obvious.

    Thanks for the tip about addslashes.

    duh, no, the 20 iterations of q, m, & i all contain distinct values.

    What I am trying to accomplish is to avoid an insert query that has 63 values, all surrounded by 'mysql_real_escape_string', when i can cut that down to 3 with a for loop.

  4. #4
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,853
    I think traq means the same type of values - they all contain the same kind of information (otherwise why call them q1, q2, q3, .... etc.?). Having columns named like that (and queries assembled the way you're doing it) is a very strong sign that your database design is broken. Does every Recipe always have exactly twenty Qty, Ing, and Mea things each? What do you do with recipes that have fewer? Or more?

    It looks like you're missing the "relational" from your "relational database". Read up on "First Normal Form" because from what we've seen your database lacks it.
    Last edited by Weedpacket; 01-28-2013 at 11:58 PM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  5. #5
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    Quote Originally Posted by timstring View Post
    I said the solution would be painfully obvious.

    Thanks for the tip about addslashes.
    You're welcome.
    You would be well advised to also consider the warning against the entire mysql extension.

    Quote Originally Posted by timstring View Post
    duh, no, the 20 iterations of q, m, & i all contain distinct values.

    What I am trying to accomplish is to avoid an insert query that has 63 values, all surrounded by 'mysql_real_escape_string', when i can cut that down to 3 with a for loop.
    Why? Why would you favor 20 queries over one that does the same job? That's horribly inefficient. Round-trips to the database are one of the most time-consuming parts of a PHP script.

    As Weedpacket says, what I was getting at is the simple fact that you have sixty-three columns with mostly identical names in a single table strongly suggests that you need to redesign your tables.

    Maybe:
    Code:
    CREATE TABLE `recipe`(
       `id` INT NOT NULL AUTO_INCREMENT 
          COMMENT 'this is your primary key'
      ,`title` VARCHAR(100) NOT NULL 
          COMMENT 'the name of the recipe - 
          if this is UNIQUE it could be a natural key,
          and you wouldnt need the `id` col at all'
      ,PRIMARY KEY( `id` )
    );
    
    CREATE TABLE `ingredient`(
       `id` INT NOT NULL AUTO_INCREMENT
      ,`recipe_id` INT NOT NULL
      ,`quantity` INT NOT NULL
      ,`measure` VARCHAR(100) NOT NULL
         COMMENT 'Im assuming this is the unit of measure for `quantity`...?'
      ,`ingredient` VARCHAR(100) NOT NULL
      ,PRIMARY KEY( `id` )
      ,FOREIGN KEY( `recipe_id` ) REFERENCES `recipe`( `id` )
          ON UPDATE CASCADE
          ON DELETE CASCADE
    );
    -- you're not limited to 20 ingredients, nor do you have empty columns in any rows.
    -- all ingredients belong to a recipe.
    -- if you delete a recipe, its ingredients are gone as well.

    You'd need to use the INNODB engine, rather than the default MyISAM.
    Even if you don't use the foreign key, this structure would simplify your database, how you manage it, and also allow for more efficient reading.
    Last edited by traq; 01-29-2013 at 12:34 AM.

  6. #6
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    I am trying to follow your logic, and I have thee questions for now. I'm sure I'll more to ask later. What is the significance of `recipe_id`? How is this number generated? Right now, all the rows have values of 0.

    Here's the more pressing need: I have three tables that need to relate to one another. Here's the structure:

    Table `CourtneyTechs` is the master file and has `TechNum` & `CellNum` among other fields. `TechNum` is the primary key, but `CellNum` is also unique.
    Table `CallCount` is one data file with 57,215 records, keyed to `IdNum` containing `CellNum` & data fields.
    Table `WorkOrders` is another data file of the same size, keyed to `IdNum`, and containing `TechNum` & data fields.

    What I need to happen is this: `CourtneyTechs` says to `CallCount`: "Here's the `CellNum` for tech John Smith. Give me all the rows that have the same `CellNum`. Then, I need `CourtneyTechs` to say to `WorkOrders`: "Here's the `TechNum` for John Smith. Give me all the rows that have the same `TechNum`, and so forth until the all the rows in `CourtneyTechs` have been processed.

    I was able to create the two tables with the and import the .csv files I have. Now, my 2nd question is: After I created the two tables with Foreign Keys, Navicat says that the name of one foreign keys is "1workorders_ibfk_1", and the name of the 2nd table is equally puzzling. How is that name generated, and what is its significance?

    Oops, I need to ask a 4th question: How do I get the data out of the two tables? If I execute a 'SELECT * ' on `CourtneyTechs` will that automatically pull the data from the two subjugated tables? I would assume so, but asking almost never hurts.

    Oh No!! Number 5! If I set up a form to insert data, how would MySQL know to call up another row? Is this automatic, or does some trick with PHP need to be executed?

    Are all these questions as clear as mud? The answers will probably be long enough to write a master's thesis, when I'm just a lowly apprentice. Thanks as always for all the help.

    tim

  7. #7
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    recipe_id should be the same value as the id of the recipe it belongs to (i.e., if an ingredient belongs to recipe #42, then recipe_id would be be 42). That way, you can join the ingredients with the correct recipe.

    ------------
    and yes, your other questions are about as clear as mud. I've got the idea of what you're asking, but before I answer:

    1) Could you show me the actual table schemas (SQL code to create the tables)? Much better than trying to infer the structure from a description. (You can use the query "SHOW CREATE TABLE tablename" to get this; just cut-n-paste the output here.)

    2) Queries should be fairly straightforward. We'll deal with this after you show me your schemas.

    3) I've never used Navicat. I have no idea how it decides things. If it's at all worthwhile, it should allow you to choose (and name) your own foreign keys.

    4) No. As I said, this isn't too complicated, but let's see the table structure first.

    5) I'm not certain what this question means.

  8. #8
    Senior Member
    Join Date
    Sep 2012
    Posts
    269

    Exclamation

    CourtneyTechs:

    Code:
    CREATE TABLE `CourtneyTechs` (
      `System` varchar(30) NOT NULL,
      `TechNum` int(6) NOT NULL,
      `FirstName` varchar(25) NOT NULL,
      `LastName` varchar(25) NOT NULL,
      `CellNum` int(15) NOT NULL,
      `AltCell` varchar(15) DEFAULT NULL,
      PRIMARY KEY (`TechNum`),
      UNIQUE KEY `CourtneyTechs` (`TechNum`) USING BTREE,
      KEY `CellNum` (`CellNum`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    Here's the code for `1CallCount`:

    Code:
    CREATE TABLE `1CallCount` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `CourtneyTechs_Id` int(6) NOT NULL,
      `CellPhone` int(11) NOT NULL,
      `CallCount` int(11) NOT NULL
      PRIMARY KEY (`id`),
      KEY `1callcount_ibfk_1` (`CourtneyTechs_Id`),
      CONSTRAINT `1callcount_ibfk_1` FOREIGN KEY (`CourtneyTechs_Id`) REFERENCES `CourtneyTechs` (`CellNum`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=57216 DEFAULT CHARSET=latin1

    `1WorkOrders`:
    Code:
    CREATE TABLE `1WorkOrders` (
      `IdNum` int(11) NOT NULL AUTO_INCREMENT,
      `TechNum` int(11) NOT NULL,
      `1WorkerOrders_Id` int(11) NOT NULL,
      `CellNum` int(11) NOT NULL,
      `WorkOrders` int(11) NOT NULL,
      `DI` int(11) DEFAULT NULL,
      `DW` int(11) DEFAULT NULL,
      `IN` int(11) DEFAULT NULL,
      `NP` int(11) DEFAULT NULL,
      `RS` int(11) DEFAULT NULL,
      `SR` int(11) DEFAULT NULL,
      `TC` int(11) DEFAULT NULL,
      `UP` int(11) DEFAULT NULL,
      PRIMARY KEY (`IdNum`),
      KEY `1WorkerOrders_Id` (`1WorkerOrders_Id`),
      CONSTRAINT `1workorders_ibfk_1` FOREIGN KEY (`1WorkerOrders_Id`) REFERENCES `CourtneyTechs` (`TechNum`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=82200 DEFAULT CHARSET=latin1
    The more I read through this post, the less I understand.
    Last edited by timstring; 02-02-2013 at 06:40 PM.

  9. #9
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    Quote Originally Posted by timstring View Post
    How do I get the data out of the two tables? If I execute a 'SELECT * ' on `CourtneyTechs` will that automatically pull the data from the two subjugated tables? I would assume so, but asking almost never hurts.
    No, and you should be specifying the names of the columns you want. * is almost never the right solution.

    Quote Originally Posted by timstring View Post
    What I need to happen is this: `CourtneyTechs` says to `CallCount`: "Here's the `CellNum` for tech John Smith. Give me all the rows that have the same `CellNum`. Then, I need `CourtneyTechs` to say to `WorkOrders`: "Here's the `TechNum` for John Smith. Give me all the rows that have the same `TechNum`, and so forth until the all the rows in `CourtneyTechs` have been processed.
    Before answering this, I have other questions:
    Code:
    FOREIGN KEY (`CourtneyTechs_Id`) REFERENCES `CourtneyTechs` (`CellNum`)
    Is that a typo? Why would the foreign key "CourtneyTechs_Id" reference CourtneyTechs.Cellnum?

    Likewise,
    Code:
    FOREIGN KEY (`1WorkerOrders_Id`) REFERENCES `CourtneyTechs` (`TechNum`)
    Also, from your question above, there is no CellNum column in the CallCount table. I would assume that you're comparing to the CellPhone column, but you know what they say about assumptions.

    I would recommend clearing up these problems before you start trying to create queries. Are you still in the process of designing this, or is it already in production?

    (Also, personally, I prefer all lowercase table/column names - they're easier to type, and also to remember. It can get confusing when you have to memorize which letters/words/partsofwords are capitalized, especially when the same rules don't apply to each name (e.g.: 1CallCount.id vs. 1WorkOrders.IdNum). Likewise with pluralization (CourtneyTechs [plural] vs. 1CallCount [singular]), [seemingly] arbitrary numbers in the table names, and downright "odd" column names (1WorkerOrders_Id - huh?).)

    Quote Originally Posted by timstring View Post
    Oh No!! Number 5! If I set up a form to insert data, how would MySQL know to call up another row? Is this automatic, or does some trick with PHP need to be executed?
    I'm still not sure what you're really asking, but no, you need to write deliberate INSERT queries for all the data (for each table) you want to insert. No tricks, just queries.

  10. #10
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    Try number two:

    Code:
    CREATE TABLE `CourtneyTechs` (
      `SystemNum` int(11) NOT NULL,
      `System` varchar(30) NOT NULL,
      `TechNum` int(6) NOT NULL,
      `FirstName` varchar(25) NOT NULL,
      `LastName` varchar(25) NOT NULL,
      `CellNum` int(15) NOT NULL,
      `AltCell` int(11) DEFAULT NULL,
      PRIMARY KEY (`TechNum`),
      UNIQUE KEY `CourtneyTechs` (`TechNum`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    Code:
    CREATE TABLE `WorkOrders`(
       `IdNum` INT NOT NULL AUTO_INCREMENT
      ,`CourtneyTechs_IdNum` INT NOT NULL
      ,`TechNum` INT NOT NULL
      ,`WorkOrders` INT NOT NULL
      ,PRIMARY KEY( `IdNum` )
      ,FOREIGN KEY( `TechNum` ) REFERENCES `CourtneyTechs`( `TechNum` )
          ON UPDATE CASCADE
          ON DELETE CASCADE
    So far as I can tell, this script had the desired results.

    This one, however, returns an error:

    Code:
    CREATE TABLE `CallLog`(
       `IdNum` INT NOT NULL AUTO_INCREMENT
      ,`CourtneyTechs_IdNum` INT NOT NULL
      ,`CellNum` INT NOT NULL
      ,`WorkDate` DATE NOT NULL
      ,`CallCount` INT NOT NULL
      ,PRIMARY KEY( `IdNum` )
      ,FOREIGN KEY( `CellNum` ) REFERENCES `CourtneyTechs`( `CellNum` )
          ON UPDATE CASCADE
          ON DELETE CASCADE
    );
    I'm getting an error code 150. `CellNum` and `CourtneyTechs`.`CellNum` are both defined as integers. What am I missing?

    The more I know, the less I understand

  11. #11
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    Quote Originally Posted by timstring View Post
    Code:
    `CourtneyTechs`.`CellNum` int(15) NOT NULL
          `CallLog`.`CellNum` INT NOT NULL
    I'm getting an error code 150. `CellNum` and `CourtneyTechs`.`CellNum` are both defined as integers. What am I missing?
    They need to be the same size.

  12. #12
    Senior Member
    Join Date
    Mar 2009
    Posts
    802
    Quote Originally Posted by traq View Post
    They need to be the same size.
    Not really pertinent to this situation, but I happened to read last night that they also need to be "signed" the same (either both "signed" or "unsigned").
    Declare variables, not war.

  13. #13
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,767
    The column definition need to be able to store exactly the same information. Hence needing the same size, signed, type, etc.
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

  14. #14
    Senior Member traq's Avatar
    Join Date
    Jun 2011
    Location
    so.Cal
    Posts
    949
    Quote Originally Posted by Bonesnap View Post
    Not really pertinent to this situation, but I happened to read last night that they also need to be "signed" the same (either both "signed" or "unsigned").
    indeed.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •