1

i have 4 tables like this

  table0plus                 table10plus                    table20plus
[id  -   name ]              [id -  name ]               [id  -  name  -  age]
[1   -  garick]              [1  -  nizak]               [1  -  karin  -  25 ]
[2   -  garry ]              [2  -  nimar]               [2  -  kuster -  28 ]

    tabletype
[ name    -   type  ]
[ garry   -   male  ]
[ nizak   -  female ]
[ karin   -  female ]
[ nimar   -   male  ]

I'm trying to select all the values from all the tables, And what i've tried is

$query = "
SELECT a.id, a.name, b.type, '-20' AS age FROM table0plus a LEFT JOIN tabletype b ON a.name = b.name WHERE id = :id
UNION ALL
SELECT a.id, a.name, b.type, '-10' AS age FROM table10plus a LEFT JOIN tabletype b ON a.name = b.name WHERE id = :id
UNION ALL
SELECT a.id, a.name, b.type, a.age FROM table20plus a LEFT JOIN tabletype b ON a.name = b.name WHERE id = :id
ORDER BY age
LIMIT :limit
";

But it won't work and gives me an error.

I'm trying to output the values ordered by date or name or age and all limited. How can i do that correctly?

the error is 

Fatal error
: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number

and my PDO code is

$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $conn->prepare($query);
$stmt->execute(['id' => $id, 'limit' => $limit]);

tried to execute 'id' => $id 3 times but still the same problem.

Axon
  • 439
  • 1
  • 4
  • 13
  • 1
    And the error....can you add it to your question? – Hackerman Aug 25 '17 at 20:15
  • did you try to paste your code into sql engine and run it there? i'm sure it would show you are missing commas – MIKE Aug 25 '17 at 20:15
  • @MIKE just noticed i wrote it here wrong, fixed that. – Axon Aug 25 '17 at 20:18
  • I don't understand why you need multiple tables for this, that sounds like a waste of space and more importantly a waste of time considering all the extra hoops you will have to jump through for this data to be useful – GrumpyCrouton Aug 25 '17 at 20:24
  • @GrumpyCrouton is there a better way? I thought about joining them all, But i got 2 tables without age column, so didn't know what to do about that. – Axon Aug 25 '17 at 20:25
  • 1
    Just make it so the age column can be NULL? Or make a column like "group-set" or something like that where you can store the group they should be in. (E.g "10+", "20+"). I mean it's almost never a good idea to have multiple tables that hold basically the exact same type of data – GrumpyCrouton Aug 25 '17 at 20:26
  • Maybe binding the param and then executing? $stmt = $conn->prepare($query); $stmt->bindParam(':id', $id, PDO::PARAM_INT); $stmt->execute(); – Carlos Alves Jorge Aug 25 '17 at 20:26
  • @GrumpyCrouton This is a small example of the actual tables, I can't add or remove any rows, So i just add this example trying to solve the problem on it. – Axon Aug 25 '17 at 20:28
  • 1
    Yes but if you just fixed your table structures, you wouldn't have your problem anymore. It would be much simpler to use all of your data. – GrumpyCrouton Aug 25 '17 at 20:29
  • @GrumpyCrouton For now i'm planning to do that, But currently i need to solve this problem first to get the site working. till i rework the tables again. – Axon Aug 25 '17 at 20:32
  • 1
    Sure, take a few hours to write code to fix the issue you have with bad table design, redesign your table structure, then spend a few more hours rewriting the code you just fixed. – GrumpyCrouton Aug 25 '17 at 20:34
  • 1
    1) You cannot bind the limit parameter. 2) Unless you turn on (I think) EMULATE_PREPARES, you cannot reuse the parameter. – aynber Aug 25 '17 at 20:34
  • 1
    https://stackoverflow.com/questions/2432084/pdo-parameterized-query-reuse-named-placeholders is the dup about reusing placeholders – aynber Aug 25 '17 at 20:35
  • @aynber So should i turn the emulator on and try to bind the limit? – Axon Aug 25 '17 at 20:39
  • 1
    Turn the emulator on, and do NOT bind the limit. Instead, verify that it's an integer and pass that directly into the query. – aynber Aug 25 '17 at 20:39
  • I'm going to agree with GrumpyCrouton here. This problem may go beyond "just make it work". The time and energy you're expending to hack something together would be much better spent actually fixing the underlying problems. – Shawn Aug 25 '17 at 20:43
  • @Shawn He is absolutely correct, But i'm in hurry to solve it for now to redesign while it is working, At the same time, I want to learn how to solve a problem like it if faced me again. And it appears that it is from the PDO configuration itself thanks to anyber – Axon Aug 25 '17 at 20:45
  • @aynber by passing it directly, Do you mean like this example https://stackoverflow.com/a/2269931/8351025 ? but what i see is that he did bind it, Or check it using PHP `if(is_int($limit)){..continue the code..}` and pass it directly like `$query = "..... LIMIT $limit;" ` – Axon Aug 25 '17 at 20:49
  • 1
    Right, or you can do `$limit = (int)$limit;`, which will cast the [whatever limit is into an integer](http://php.net/manual/en/language.types.type-juggling.php) – aynber Aug 25 '17 at 20:52
  • @aynber that solved, If possible put it as answer to select it, – Axon Aug 25 '17 at 20:57

0 Answers0