0

Below is the query to find total tickets of a person. fWork is that person's ID. What is the best way to put the variable inside the condition?

Should I declare something like $ID = $_GET['ID']; and it becomes fWork = $ID

or fWork = ?, and put $_GET[ID] in the parameter like this array($_GET['ID'])

$open_count = " SELECT * FROM TicketO  WHERE Assigned = 0 AND fWork = 983";
$query = sqlsrv_query($NEI,$open_count,  array(), array( "Scrollable" => 'buffered' ));
$open_tickets_count = sqlsrv_num_rows($query);
echo " open tickets: " .  $open_tickets_count;

Also, I am having trouble including UNION ALL in the query, am i missing some parentheses? It returns false.

$total_count = "SELECT * FROM TicketO WHERE fWork = '983' 
                UNION ALL 
                SELECT * FROM TicketD WHERE fWork = '983'";
cheunology
  • 41
  • 5
  • 1
    The first question has to be... Are the `TicketO` and `TicketD` tables an exact duplicate of each other i.e. the same datatypes in the same order? – RiggsFolly Jun 29 '18 at 17:21
  • no they are not. Should I use Union instead – cheunology Jun 29 '18 at 17:39
  • @cheunology You are combining 2 tables into 1, so their structure (# of columns, type of each column) must be the same. It's common sense. How would you coming 2 tables that have different structures??? – Eric Jun 29 '18 at 18:26
  • Maybe you should show us the schema for both tables and ALSO the columns that you are actually interested in retrieving – RiggsFolly Jun 29 '18 at 18:38
  • I would use explicit column names in the SELECT .. UNION .. SELECT. The usage of `*` can be problematic, especially when the underlying types are different and/or change. – user2864740 Jun 29 '18 at 18:56
  • If both tables return the same columns in the same order, and there is a fWork column of the appropriate type, then the $total_count query will run without error; [the documentation shows that no parenthesis are required](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-2017) .. it helps to only have one "problem" per SO question. – user2864740 Jun 29 '18 at 19:00

1 Answers1

-1

First I recommend you to validate that the ID come in the request

$fWork = '';
if(isset($_GET['ID']))
    $fWork = $_GET['ID'];

 $total_count = "SELECT * FROM TicketO as O WHERE O.fWork = '".$fWork."' 
                 UNION ALL 
                 SELECT * FROM TicketD as D WHERE D.fWork = '".$fWork."'";

I can not tellyou why the UNION ALL do not work, I need to see the tables structure for that, but remembres that in a UNION ALL you have to return the same quantity of colums, and these need to have the same data type

eduCan
  • 180
  • 1
  • 9
  • 1
    So we should ignore the [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) danger should we? – RiggsFolly Jun 29 '18 at 17:19
  • Yes, is very unsecure, if you want to avoid SQL injection please use PrepareSatatement, here ->https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php you can see how use it with MySqli and PDO – eduCan Jun 29 '18 at 17:26
  • Now OP says the tables are not exact duplicates so the UNION will not work for that reason – RiggsFolly Jun 29 '18 at 18:38