[PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
Anyone? Please. 2012/9/18 Bruno Sandivilli > Hi, i strugling to decide what is the best choice: > > I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have > 3 values. > To represent this in my Database, I could: > > >1. Create 2 Tables : A Values table - with 3 columns ; and a Bill >table (with 15 foreign keys, each one pointing to one row in the Values >table). >2. Create one Table with 45 columns (15 fields * 3 values for each >field). > > I want to know, wich is the best choice? > To manage my query, now i have a SELECT with a thousand of leftJoins. > > This is the best choice? > > How could I run a query wich will give all results linked, like: > ( column_1_val_1, column_1_val_2, column_1_val_2, > column_2_val_1, column_1_val_2, column_1_val_3, > etc...) ? > > Thanks! > >
Re: [PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
I'm a little confused here. You have a 15x3x3 set of data to display in a 3 column, 15 row datagrid? Are you displaying a single value in each cell of the datagrid, or all 3 values for the cell? How many variables are needed to uniquely select a piece of data from that 3-dimensional space? Can you provide a little more detail about the data? -Matt On 09/19/2012 11:44 AM, Bruno Sandivilli wrote: Anyone? Please. 2012/9/18 Bruno Sandivilli Hi, i strugling to decide what is the best choice: I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have 3 values. To represent this in my Database, I could: 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill table (with 15 foreign keys, each one pointing to one row in the Values table). 2. Create one Table with 45 columns (15 fields * 3 values for each field). I want to know, wich is the best choice? To manage my query, now i have a SELECT with a thousand of leftJoins. This is the best choice? How could I run a query wich will give all results linked, like: ( column_1_val_1, column_1_val_2, column_1_val_2, column_2_val_1, column_1_val_2, column_1_val_3, etc...) ? Thanks! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
On 9/18/2012 8:52 AM, Bruno Sandivilli wrote: Hi, i strugling to decide what is the best choice: I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have 3 values. To represent this in my Database, I could: 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill table (with 15 foreign keys, each one pointing to one row in the Values table). 2. Create one Table with 45 columns (15 fields * 3 values for each field). I want to know, wich is the best choice? To manage my query, now i have a SELECT with a thousand of leftJoins. This is the best choice? How could I run a query wich will give all results linked, like: ( column_1_val_1, column_1_val_2, column_1_val_2, column_2_val_1, column_1_val_2, column_1_val_3, etc...) ? Thanks! I don't get it either. You have 15 rows with 3 cols each. So? Display them. Query them. Big deal. What is the real difficulty here? I'm not seeing it. (I don't read it as 15x3x3 - at least that's not what he said since he said 'for each row he has 3 values') -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
On 9/19/2012 3:12 PM, Jim Giner wrote: On 9/18/2012 8:52 AM, Bruno Sandivilli wrote: Hi, i strugling to decide what is the best choice: I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have 3 values. To represent this in my Database, I could: 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill table (with 15 foreign keys, each one pointing to one row in the Values table). 2. Create one Table with 45 columns (15 fields * 3 values for each field). I want to know, wich is the best choice? To manage my query, now i have a SELECT with a thousand of leftJoins. This is the best choice? How could I run a query wich will give all results linked, like: ( column_1_val_1, column_1_val_2, column_1_val_2, column_2_val_1, column_1_val_2, column_1_val_3, etc...) ? Thanks! I don't get it either. You have 15 rows with 3 cols each. So? Display them. Query them. Big deal. What is the real difficulty here? I'm not seeing it. (I don't read it as 15x3x3 - at least that's not what he said since he said 'for each row he has 3 values') Continuing on - why is your visual structure at this point NOT the same as your physical structure? I don't think you're telling us what your real problem is here. We need more information. You have '15 row with 3 cols each', ie, 'each row has 3 values'. So - your table has 15 records in it, each with 3 columns. Add a key field to give each row an identity and that's it. No? Sorry - but again - this post is not showing me a clear problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
Sorry for the lack of explanation. Again: I have to model a bill table. The bill have 20 fields. But for each field we have: *registered* value, *billed *value and* total value.* So i've created an *bill* table with *20 *fields (representing the bill fields, like: consumption, demand, etc ), and a *bill_values* table (with *id*,*registered*,*billed *and *total*). I'll try to ilustrate: Bill Table *ID | eletricity_use | eletricity_demand_ | eletricity_traffic_use | eletricity_penalties *| etc 1 101102103 104 *AND* Value Table *ID | Registered | Billed | Total* 101 120,00 150,12102,36 102 130,00 150,12102,36 103 150,00 150,12102,36 104 110,00 140,12102,36 better? Thanks! 2012/9/19 Jim Giner > On 9/19/2012 3:12 PM, Jim Giner wrote: > >> On 9/18/2012 8:52 AM, Bruno Sandivilli wrote: >> >>> Hi, i strugling to decide what is the best choice: >>> >>> I have a 15 row x 3 columns Flash DataGrid, it means, for each row i >>> have 3 >>> values. >>> To represent this in my Database, I could: >>> >>> >>> 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill >>> table >>> (with 15 foreign keys, each one pointing to one row in the Values >>> table). >>> 2. Create one Table with 45 columns (15 fields * 3 values for each >>> field). >>> >>> I want to know, wich is the best choice? >>> To manage my query, now i have a SELECT with a thousand of leftJoins. >>> >>> This is the best choice? >>> >>> How could I run a query wich will give all results linked, like: >>> ( column_1_val_1, column_1_val_2, column_1_val_2, >>>column_2_val_1, column_1_val_2, column_1_val_3, >>> etc...) ? >>> >>> Thanks! >>> >>> I don't get it either. >> >> You have 15 rows with 3 cols each. So? Display them. Query them. Big >> deal. >> >> What is the real difficulty here? I'm not seeing it. >> >> (I don't read it as 15x3x3 - at least that's not what he said since he >> said 'for each row he has 3 values') >> > Continuing on - why is your visual structure at this point NOT the same as > your physical structure? I don't think you're telling us what your real > problem is here. We need more information. You have '15 row with 3 cols > each', ie, 'each row has 3 values'. So - your table has 15 records in it, > each with 3 columns. Add a key field to give each row an identity and > that's it. No? > > Sorry - but again - this post is not showing me a clear problem. > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
Bruno, This schema layout seems a little odd to me. Maybe I'm just misunderstanding the use. Correct me if I'm wrong here: Each row in the "bill" table is a customer? (or some other lookup value for which you're generating the data grid?) I assume the 15 fields from your original message are the 20 fields you are describing here? The values inside the Bill table are 100% unique and point to the Value table? You're trying to generate output that looks like this? CategoryRegisteredBilledTotal electricity_use 120,00 150,12102,36 electricity_demand130,00 150,12102,36 ... for a particular row in the Bill table where ID=something ? If this is true, I would consider normalizing this a bit more. If you break the columns in your bill table out into their own table, expand your values table a bit, you could end up with something like this: categories table: idcategory_name 1 electricity_use 2 electricity_demand ...... (where id is the primary key) bill_data table: ID_from_original_bill_tablecategory_idregistered billedtotal 1 1 120,00 150,12102,36 1 2 120,00 150,12102,36 ... (where (ID_from_original_bill_table, category_id) is the primary key) And do queries like this: SELECT c.category_name, bd.registered, bd.billed, bd.total FROM categories AS c JOIN bill_data AS bd ON c.id=bd.category_id WHERE bill_data.ID_from_original_bill_table="whatever"; Or am I still not understanding correctly what you're trying to do? If you actually need to do a pivot you might want to look here: http://www.artfulsoftware.com/infotree/queries.php#78 -Matt On 09/19/2012 01:33 PM, Bruno Sandivilli wrote: Sorry for the lack of explanation. Again: I have to model a bill table. The bill have 20 fields. But for each field we have: *registered* value, *billed *value and* total value.* So i've created an *bill* table with *20 *fields (representing the bill fields, like: consumption, demand, etc ), and a *bill_values* table (with *id*,*registered*,*billed *and *total*). I'll try to ilustrate: Bill Table *ID | eletricity_use | eletricity_demand_ | eletricity_traffic_use | eletricity_penalties *| etc 1 101102103 104 *AND* Value Table *ID | Registered | Billed | Total* 101 120,00 150,12102,36 102 130,00 150,12102,36 103 150,00 150,12102,36 104 110,00 140,12102,36 better? Thanks! 2012/9/19 Jim Giner On 9/19/2012 3:12 PM, Jim Giner wrote: On 9/18/2012 8:52 AM, Bruno Sandivilli wrote: Hi, i strugling to decide what is the best choice: I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have 3 values. To represent this in my Database, I could: 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill table (with 15 foreign keys, each one pointing to one row in the Values table). 2. Create one Table with 45 columns (15 fields * 3 values for each field). I want to know, wich is the best choice? To manage my query, now i have a SELECT with a thousand of leftJoins. This is the best choice? How could I run a query wich will give all results linked, like: ( column_1_val_1, column_1_val_2, column_1_val_2, column_2_val_1, column_1_val_2, column_1_val_3, etc...) ? Thanks! I don't get it either. You have 15 rows with 3 cols each. So? Display them. Query them. Big deal. What is the real difficulty here? I'm not seeing it. (I don't read it as 15x3x3 - at least that's not what he said since he said 'for each row he has 3 values') Continuing on - why is your visual structure at this point NOT the same as your physical structure? I don't think you're telling us what your real problem is here. We need more information. You have '15 row with 3 cols each', ie, 'each row has 3 values'. So - your table has 15 records in it, each with 3 columns. Add a key field to give each row an identity and that's it. No? Sorry - but again - this post is not showing me a clear problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Unsubscribe me please
Hi, I tried unsubscribing from the list, but I was unsuccessful, I believe because when I subscribed I cleverly added +php to the end of my email address (for Gmail filtering). Could I be removed please? Thanks. -- Graham Holtslander Computer Systems Technologist www.graham.holtslander.com mene...@gmail.com
[PHP-DB] Prepared Statements - Select
Dear List -
Thanks to all for your responses.
Here is another one
I wish to accomplish the following
mysqli_stmt_bind_param($stmt, 'sis', $_POST['Site'], $_POST['MedRec'],
$_POST['Sex']);
This statemnt was hand coded. I wish to be able to generalize it.
Therefore -
$sql11 = "SELECT Site, MedRec, Fname, Lname, Phone, Height,
Sex, Hx, Bday, Age FROM Intake3 where 1 ";
$allowed_fields = array
( 'Site' =>$_POST['Site'], 'MedRec' => $_POST['MedRec'],
'Fname' => $_POST['Fname'], 'Lname' => $_POST['Lname'] ,
'Phone' => $_POST['Phone'] , 'Height' =>
$_POST['Height'], 'Sex' => $_POST['Sex'], 'Hx' => $_POST['Hx'],
'Bday' => $_POST['Bday'], 'Age' => $_POST['Age'] );
$z0='$_POST';
$z0 .="['Site']";
$z1='$_POST';
$z1 .="['MedRec']";
$z2='$_POST';
$z2 .="['Fname']";
.
.
.
$indeces = array(
"0" => array
(
'tpe'=> 's',
"val" => $z0
),
"1" => array
(
"tpe" => "i",
"val"=> $z1
),
.
.
$binder = array(); //array to hold variables
$typer = array(); //array to hold variable type
$position = -1;
foreach ( $allowed_fields as $key => $val )
{
$position = $position + 1;
if ($val != '')
{
array_push($binder, $indeces[$position]['val']);
array_push($binder, ", ");
array_push($typer, $indeces[$position]['tpe']);
$sql11 .= " AND ($key = ?) ";
}
}
array_pop($binder);
The above generates the following query:
SELECT Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age FROM
Intake3 where 1 AND (Site = ?) AND (MedRec = ?) AND (Sex = ?)
//Construct the strings for the mysqli_stmt_bind_param statement
$typ2 = implode($typer);
$typ3 = "'";
$typ3 .=$typ2;
$typ3 .= "'";
$bind3 = implode($binder);
//This statement works perfectly. mysqli_stmt_bind_param($stmt, 'sis',
$_POST['Site'], $_POST['MedRec'], $_POST['Sex']);
//This one fails
mysqli_stmt_bind_param($stmt, $typ3, $bind3);
With the following error message:
Warning: mysqli_stmt_bind_param(): Number of elements in type definition string
doesn't match number of bind variables
echo "$typ3";'sis'
echo " $bind3"; $_POST['Site'], $_POST['MedRec'], $_POST['Sex']
Help and Advice, please
Ethan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Unsubscribe me please
Unsubscribe me too leckyt...@gmail.com On 19 September 2012 23:43, Graham H. wrote: > Hi, > > I tried unsubscribing from the list, but I was unsuccessful, I believe > because when I subscribed I cleverly added +php to the end of my email > address (for Gmail filtering). > > Could I be removed please? Thanks. > > -- > Graham Holtslander > Computer Systems Technologist > www.graham.holtslander.com > mene...@gmail.com >
