Re: [PHP-DB] Any method to get primary key matching a given value ?

[B. Aerts]

Hi Ratin,

check out this FAQ : https://sqlite.org/faq.html#q7

SQlite has a read-only table that holds the creation query for each table.

By doing a text analysis of this query, you can find out which field is 
declared as primary key.


Regards,

Bert

On 11/10/16 02:12, Ratin wrote:

Sorry about my late reply but this was my function - a bit of a hack but
works properly on my version of php-sqlite3:


  function get_primary_key_name($table)
  {
   $primary_key='';
   $db = new MyDB();
   if(!$db)
   {
   echo $db->lastErrorMsg();
   }
   else
   {
   $qstr = "PRAGMA table_info(" . $table . ");" ;
   $query = $db->query($qstr);
   while ($result = $query->fetchArray())
   {
  if ($result['pk'] == 1)
  {
$primary_key=$result['name'];
   }
}
   }
   $db->close();
   return $primary_key;
   }


On Sat, Aug 20, 2016 at 3:35 AM, Karl DeSaulniers 
wrote:


This may also shed some light for you.
The accepted answer and possibly the one below it if you are on .NET

http://stackoverflow.com/questions/763516/information-
schema-columns-on-sqlite

HTH,

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com





On Aug 20, 2016, at 5:30 AM, Karl DeSaulniers 

wrote:


Hey Ratin,
Have you looked into the table column named 'pk' inside table_info?
That is where a column is indicated to be a primary key or not.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com 





On Aug 18, 2016, at 6:51 PM, Ratin  wrote:

Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA is

not available for sqlite database. I had to built up the whole query with
php using PRAGMA table_info(tablename), looking at the pk entry, when its
1, get the column name, and then update the sql statement based on that. A
bit of work, wouldve been much simpler if a method was provided, but oh
well ..


Thanks again

Ratin

On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers > wrote:

Hi Ratin,
Going to take a stab at this one.
Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
Might be where you want to look for what you are trying.
Sorry can't help more.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com 





On Aug 18, 2016, at 1:27 PM, Ratin > wrote:


I'm writing the generic get that works on different tables having

different

primary keys but the argument of get is always the primary key , i.e.

get

request is -

get (column name, value)

the value is always the primary key value.

It looks like it would be a pretty standard method but I cant find a

method

like that. Anybody have any clue?

Thanks

Ratin



--
PHP Database Mailing List (http://www.php.net/ )
To unsubscribe, visit: http://www.php.net/unsub.php <

http://www.php.net/unsub.php>








--
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

Re: [PHP-DB] Corn job anomaly

[B. Aerts]

On 20/09/16 10:06, Lester Caine wrote:

On 20/09/16 06:16, Karl DeSaulniers wrote:

Was probably a newb question, however, now it is saying that my database user 
is not allowed access.
I have a mysql connection inside my script that reads the database to get user 
email addresses to send a reminder email to.

Is there supposed to be a call or directive to load mysql in my command line as 
well?
Man this is frustrating.


The user name and password you are using from the web scripts SHOULD
work in the script you are using on the cron job. Although mysql does
seem to have a few extra security features that cut in. Are they listed
in the script, or loaded from the environment ... which is of cause
different for the cron jobs ... and why you needed the full path.



Karl,

is your MySQL database located on the machine itsself where you run the 
Cron job ?
If so, does your Cron user have read/write/execute permissions on either 
the DB directory, and the DB itsself ?


Regards, Bert

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] MySQL two tables and an uneven number of rows

[B. Aerts]

On 13/09/16 08:42, Karl DeSaulniers wrote:

On Sep 12, 2016, at 2:53 PM, B. Aerts <ba_ae...@yahoo.com> wrote:

On 12/09/16 05:24, Karl DeSaulniers wrote:

Hello All,
Hoping you can help clear my head on this. I have two MySQL tables for custom 
fields data to be stored.

custom_fields   custom_fields_meta

custom_fields is the info for the actual field displayed in the html and 
custom_fields_meta is the data stored from entering a value on said field in 
the form.

Custom fields can be added and removed at will by the user and so when for 
instance, adding a field,
it currently creates an uneven number of rows in the custom_fields_meta if 
there were any entries with fields create prior to this new one.

Currently I have this code:

$SQL = "SELECT ft.*, mt.Meta_Value
FROM `CUSTOM_FIELDS` ft
LEFT JOIN `CUSTOM_FIELDS_META` mt
ON mt.Field_ID = ft.Field_ID
WHERE mt.Order_ID=%d
ORDER BY ft.Field_ID ASC";

I have tried JOIN, FULL JOIN, FULL OUTER JOIN, OUTER JOIN and LEFT JOIN.
If I manually put in the missing rows in the meta table, left join works.
However, manually updating prior entries is not going to happen.

So my question is how do I get all the table rows in both tables even if there 
is not a row to match on the meta table?
or
How would I update the prior entries to include this new field in the meta 
table and keep things orderly?
The meta is stored per order id and so there is groups of meta data per order 
id. I would like to avoid scattered data.
Is there a way to push the index down to fit them in or is this just going to 
be too costly on server resources?

TIA,

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com <http://designdrumm.com/>






Hi Karl,

I can't really follow your problem ... Any chance to post 2 dummy table layouts 
to show what you want, and what you get ?

And it isn't something you could solve with a UNION ?





Hello,
Thanks fro your reply. I can try. :)

BEFORE:

CUSTOM_FIELDS:

`Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, 
`Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, 
`Field_Required`, `Field_Date_Created`
||
(1, 'Pickup Info', 'Phone 1', 'Origin_Phone1', 'origin-phone1', 'phone', 
'Pickup Main phone number', '', 'Yes', 'No', '2015-04-19 08:46:10'),
(2, 'Pickup Info', 'Phone 2', 'Origin_Phone2', 'origin-phone2', 'phone', 
'Pickup alternate phone number 1', '', 'Yes', 'No', '2015-04-19 08:46:11'),
(3, 'Pickup Info', 'Phone 3', 'Origin_Phone3', 'origin-phone3', 'phone', 
'Pickup alternate phone number 2', '', 'Yes', 'No', '2015-04-19 08:46:12')

CUSTOM_FIELDS_META:

`Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value`
|—|
(1, 1, 1003, '555-123-4567'),
(2, 2, 1003, ''),
(3, 3, 1003, '')


Then lets say the user wants to add a cell phone field.

AFTER:

CUSTOM_FIELDS:

`Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, 
`Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, 
`Field_Required`, `Field_Date_Created`
||
(1, 'Pickup Info', 'Phone 1', 'Origin_Phone1', 'origin-phone1', 'phone', 
'Pickup Main phone number', '', 'Yes', 'No', '2015-04-19 08:46:10'),
(2, 'Pickup Info', 'Phone 2', 'Origin_Phone2', 'origin-phone2', 'phone', 
'Pickup alternate phone number 1', '', 'Yes', 'No', '2015-04-19 08:46:11'),
(3, 'Pickup Info', 'Phone 3', 'Origin_Phone3', 'origin-phone3', 'phone', 
'Pickup alternate phone number 2', '', 'Yes', 'No', '2015-04-19 08:46:12'),
(4, 'Pickup Info', 'Cell', 'Origin_Cell', 'origin-cell', 'phone', 'Pickup cell 
phone number', '', 'Yes', 'No', '2015-04-19 08:46:13')

CUSTOM_FIELDS_META:

`Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value`
|—|
(1, 1, 1003, '555-123-4567'),
(2, 2, 1003, ''),
(3, 3, 1003, '')

The 4th field id is not in the meta table. So when I read out what is in the 
custom fields and matching meta data, cell phone does not show up for order 
that were processed before adding the custom field cell phone. I am trying to 
show the cell phone field on old orders as well even if there is not a row 
representing data in the meta table.

Hope that clears it up and not mud it up.. :P

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com



Hi Karl,

indeed this should work with a left JOIN. Did a quick test in SQLite, 
and got this to work (just to indicate that your principle is correct) :


sqlite> select * from table1 ;
1|100
2|200
4|400
sqlite> select * from table2 ;
1|1000
2|2000
3|3000
sqlite> select * from table1 join table2 on table1.ID = table2.ID ;
1|100|1|1000
2|200|2|2000
sqlite> select * from table1 left join table2 on table1.ID = table2.ID ;
1|100|1|1000
2|200|2|2000
4|400||
sqlite> select * from table1 right

[PHP-DB] Re: MySQL two tables and an uneven number of rows

[B. Aerts]

On 12/09/16 05:24, Karl DeSaulniers wrote:

Hello All,
Hoping you can help clear my head on this. I have two MySQL tables for custom 
fields data to be stored.

custom_fields   custom_fields_meta

custom_fields is the info for the actual field displayed in the html and 
custom_fields_meta is the data stored from entering a value on said field in 
the form.

Custom fields can be added and removed at will by the user and so when for 
instance, adding a field,
it currently creates an uneven number of rows in the custom_fields_meta if 
there were any entries with fields create prior to this new one.

Currently I have this code:

$SQL = "SELECT ft.*, mt.Meta_Value
FROM `CUSTOM_FIELDS` ft
LEFT JOIN `CUSTOM_FIELDS_META` mt
ON mt.Field_ID = ft.Field_ID
WHERE mt.Order_ID=%d
ORDER BY ft.Field_ID ASC";

I have tried JOIN, FULL JOIN, FULL OUTER JOIN, OUTER JOIN and LEFT JOIN.
If I manually put in the missing rows in the meta table, left join works.
However, manually updating prior entries is not going to happen.

So my question is how do I get all the table rows in both tables even if there 
is not a row to match on the meta table?
or
How would I update the prior entries to include this new field in the meta 
table and keep things orderly?
The meta is stored per order id and so there is groups of meta data per order 
id. I would like to avoid scattered data.
Is there a way to push the index down to fit them in or is this just going to 
be too costly on server resources?

TIA,

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com 






Hi Karl,

I can't really follow your problem ... Any chance to post 2 dummy table 
layouts to show what you want, and what you get ?


And it isn't something you could solve with a UNION ?

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Any method to get primary key matching a given value ?

[B. Aerts]

Hi Ratin,

working with sqlite, are you ?

In that case, take a look at the default table :

SELECT sql from sqlite_master where type= "table" and name = 
""


This query returns the creation query of the table concerned.
By parsing it textually, you can find out the field name that was 
declared PRIMARY (key)



On 19/08/16 01:51, Ratin wrote:

Hi Karl, Thanks a lot for your response, I think  INFORMATION_SCHEMA is not
available for sqlite database. I had to built up the whole query with php
using PRAGMA table_info(tablename), looking at the pk entry, when its 1,
get the column name, and then update the sql statement based on that. A bit
of work, wouldve been much simpler if a method was provided, but oh well ..

Thanks again

Ratin

On Thu, Aug 18, 2016 at 2:53 PM, Karl DeSaulniers 
wrote:


Hi Ratin,
Going to take a stab at this one.
Have you looked into INFORMATION_SCHEMA.COLUMNS for your query?
Might be where you want to look for what you are trying.
Sorry can't help more.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com





On Aug 18, 2016, at 1:27 PM, Ratin  wrote:

I'm writing the generic get that works on different tables having

different

primary keys but the argument of get is always the primary key , i.e. get
request is -

get (column name, value)

the value is always the primary key value.

It looks like it would be a pretty standard method but I cant find a

method

like that. Anybody have any clue?

Thanks

Ratin



--
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] Re: Select on Group

[B. Aerts]

On 17/11/15 01:02, Karl DeSaulniers wrote:

Hello All,
Hoping someone can help me with this query.

I want to select some custom fields from my database that are part of a group 
of custom fields.
There are several of these groups. I want to (in one sql statement) grab these 
fields, all of them
and have them grouped in the results like so.


$custom_fields = array(
'Group1' => array(
'field_ID' => '1',
'field_name' => 'myAddressField',
'filed_slug' => 'my-address-field'
),
'Group2' => array(
'field_ID' => '2',
'field_name' => 'myCityField',
'filed_slug' => 'my-city-field'
),
'Group3' => array(
'field_ID' => '3',
'field_name' => 'myStateField',
'filed_slug' => 'my-state-field'
)
)

Here is the clincher... not all the info is in the same table.
This is what I am doing currently and it works, however I would like to 
eliminate calling the database in a foreach loop as well as multiple times to 
get my results.

[code]

$FieldGroups = $wpdb->get_results("SELECT DISTINCT Field_Group FROM 
".table_name1."");

foreach($FieldGroups as $i=>$FieldGroup) {
$field_group = stripslashes_deep($FieldGroup->Field_Group);

$SQL = "SELECT ft.*, mt.*
FROM ". table_name1." ft
LEFT JOIN ". table_name2." mt
ON mt.Field_ID = ft.Field_ID
WHERE ft.Field_Group='%s' AND mt.Page_ID=%d AND 
ft.Field_Display='%s'
ORDER BY ft.Field_ID ASC"; 
$Fields = $wpdb->get_results($wpdb->prepare($SQL, $field_group, 
$Page_ID, $display));
}

[end code]

How can I combine these into one query that fills the result array the way 
described above?
I thought of a SELECT inside a SELECT, but my php foo is a little under trained 
at the moment.
Not sure how to do such and achieve the results I am after. Any help would be 
appreciated.

TIA

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com






Hi Karl,

is this all the code ?

Because, if the FOREACH() loop is running over *all* Field_Group fields, 
there is nothing to filter - might as well just run the entire INNER 
JOIN on table_name1.



In SQLite, there's an IN clause - maybe works in your database :

SELECT * from TABLE_1
WHERE Id IN ( SELECT Id from OTHER_TABLE)


Same thing can be achieved through an INNER JOIN in a more generic way :

SELECT * from TABLE_1 INNER JOIN
(SELECT * from OTHER_TABLE WHERE Id = 'criterion') AS Q1
ON TABLE_1.Id = Q1.Id

Q1, the inline query, limits the field groups to the ones requested by you.
Because it's an INNER JOIN, the join will only return matches with the 
select field groups.


Hope I'm making sense ;-)

B.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: excec / query on Sqlite3

[B. Aerts]

On 20/05/13 14:35, Gilles wrote:

Hello,

I'm not sure if it's a bug :


No, it's not - it's PHP code :-)



$o_sqlite3=new SQLite3('test.sqlite');

$r=$o_sqlite3-exec('');
echo'pre';var_dump($r);echo'/pre'; // bool(true)

$r=$o_sqlite3-query('');
echo'pre';var_dump($r);echo'/pre';  // bool(false)

$r=$o_sqlite3-exec(false);
echo'pre';var_dump($r);echo'/pre';  // bool(true)

$r=$o_sqlite3-query(false);
echo'pre';var_dump($r);echo'/pre';  // bool(false)


Best,


System Windows NT 5.1 build 2600 (Windows XP Professional Service Pack
3) i586 WIN XP SP3
PHP Version 5.4.13
SQLite3 module version 0.7
SQLite Library 3.7.7.1


Tried your code on a MacBook OS X 10.6 with PHP 5.3.15 - same result.

What's your question: the difference in response between exec() and 
query(), or the fact that a non-SQL statement does yield some result - 
albeit a boolean ?


If it's the first one, I think it is intentional:
http://be.php.net/manual/en/sqlite3.exec.php says exec() is meant for 
result-less statements. So as long as something doesn't fail, it's OK.

query() is supposed to return results - if it doesn't, something is wrong.

But that is just my interpretation of the man page.
Is there a problem beneath you're trying to solve ?

Regards,

Bert

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: Database Problems

[B. Aerts]

On 17/06/12 21:06, Ethan Rosenberg wrote:

Dear List -

I have a database:

+-+
| Tables_in_hospital2 |
+-+
| Intake3 |
| Visit3 |
+-+

mysql describe Intake3;
++-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++-+--+-+-+---+
| Site | varchar(6) | NO | PRI | | |
| MedRec | int(6) | NO | PRI | NULL | |
| Fname | varchar(15) | YES | | NULL | |
| Lname | varchar(30) | YES | | NULL | |
| Phone | varchar(30) | YES | | NULL | |
| Height | int(4) | YES | | NULL | |
| Sex | char(7) | YES | | NULL | |
| Hx | text | YES | | NULL | |
++-+--+-+-+---+

mysql describe Visit3;
++--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-++
| Indx | int(4) | NO | PRI | NULL | auto_increment |
| Site | varchar(6) | YES | | NULL | |
| MedRec | int(6) | YES | | NULL | |
| Notes | text | YES | | NULL | |
| Weight | int(4) | YES | | NULL | |
| BMI | decimal(3,1) | YES | | NULL | |
| Date | date | YES | | NULL | |
++--+--+-+-++

mysql mysql select * from Intake3 where 1 AND (Site = 'AA') AND (Sex =
'Male') ;
+--++-+---+--++--+---+

| Site | MedRec | Fname | Lname | Phone | Height | Sex | Hx |
+--++-+---+--++--+---+

| AA | 10003 | Stupid | Fool | 325 563-4178 | 65 | Male | Has been
convinced by his friends that he is obese. Normal
BMI = 23. |
| AA | 1 | David | Dummy | 845 365-1456 | 66 | Male | c/o obesity.
Various treatments w/o success |
| AA | 10001 | Tom | Smith | 984 234-4586 | 68 | Male | BMI = 20. Thinks
he is obese. |
| AA | 10007 | Foolish | Fool | 456 147-321 | 60 | Male | Thinks he is
thin. BMI = 45 |
| AA | 10005 | Tom | Obstinant | 845 368-2244 | 66 | Male | Insists that
he is not fat. Becomes violent. Psych involved. |
| AA | 10015 | Dim | Wit | 321 659-3111 | 70 | Male | Very Tall |
| AA | 10040 | Bongish | Bongish | 123 456-7890 | 50 | Male | Bong |
+--++-+---+--++--+-




The same query in a PHP program will only give me results for MedRec 10003

$allowed_fields = array
( 'Site' =$_POST['Site'], 'MedRec' = $_POST['MedRec'], 'Fname' =
$_POST['Fname'], 'Lname' = $_POST['Lname'] ,
'Phone' = $_POST['Phone'] , 'Sex' = $_POST['Sex'] , 'Height' =
$_POST['Height'] );
if(empty($allowed_fields))
{
echo ouch;
}


$query = select * from Intake3 where 1 ;

foreach ( $allowed_fields as $key = $val )
{

if ( (($val != '')) )

{
$query .=  AND ($key = '$val') ;
}

$result1 = mysqli_query($cxn, $query);

}

Ethan

MySQL 5.1 PHP 5.3.3-6 Linux [Debian (sid)]



Hello all,

Ethan, your question was: why does this work on the command line, and 
not through PHP.


The remarks by other posters still stand - you don't show anything that 
leads us to discriminate wether the fault lies in the query, in the 
API's or in the code.


If you're not going to show it, maybe try the following 2 tips :
1) try to modify the query: SELECT . . . WHERE 1=1 ;  : this 
formulation removes any doubt wether it is a filtering statement, or a 
result limiting statement


2) see if  SELECT . . . WHERE 2 or  SELECT . . . WHERE 3  yields 
respecively 2 or 3 result rows. If not, the problem is NOT with the API's.


Regards,

Bert



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Storing multiple items in one MySQL field?

[B. Aerts]

On 13/01/12 03:47, tamouse mailing lists wrote:

On Thu, Jan 12, 2012 at 1:12 AM, B. Aertsba_ae...@yahoo.com  wrote:

Karl,

I'm somewhat limited to reading posts, so I might have missed something, but
can you explain why you wanted to avoid the 3rd table solution ?

Because depending on that question, I can offer two other solutions, but
they have their own limitations.

Solution 1: the 2,5nd table.
you create an extra table and write a procedure that makes a unique ID for
every combination of attributes (and this procedure needs to be run every
time you update/add attributes).

when you want to query items for a combination of attributes, you first
select the attribute combination ID, then query your item list for all items
with this specific ID.

It isn't exactly a 3rd table solution as it doesn't involve individual
cross-references between items and a set of attributes, and thus saves you
an INNER JOIN.


Have you done any performance testing on this to see how your scheme
compares to using a 3rd table?



Nope - please check top-of-thread, the 3rd table was off-limits.
A latter without former makes a hard compare.

But depending on this application you might have a valid point: any hints ?

Bert




Solution 2: an alphanumeric bit-wise selection.
Instead of saving your combinations as binary number, you could extend it
to, for example, a string of 4 characters per attribute: 4 characters times
26 characters makes 456976 possible combinations per attribute :

define MENS   =  ;
define WOMENS = AAAB ;

define GREEN  =  ;
define RED= AAAB ;

you write one PHP function that converts an array of combinations into an
ISO wildcard filter (or does the MySQL syntax provide regular expressions
?), and apply this in a SELECT FROM ... WHERE ... LIKE statement.

$findTheseAttribs = new array ( MENS, GREEN );

$SQLlike=createSQLwildcard($findTheseAttribs); // returns 

$mySql = SELECT * FROM t_myTable WHERE itemAttribute LIKE  . $SQLlike;

The advantage is that you can assign different filter widths per attribute:
MENS/WOMENS only need 1 character, size only needs 2 ( XXXS, XXS, XS, S, ...
pretty limited), while colour can have op to 10 characters to encode. This
leaves reasonable room to expand/scale.

The drawback is that, when you add new attributes, your filter string
expands, and the LIKE-statement might fail on the difference in string
lengths.

Bert



On 12/01/12 02:18, Karl DeSaulniers wrote:


Yeah, I was being somewhat facetious about the colors of a shirt. :)
I agree on the items and attributes drill-down before implementation
though.
There will be more than T-Shirts. Watches, book-covers, etc, etc.

So I need to find a general logic to cover the items and attributes of
each item?
And this will make it scalable?

Best,

On Jan 11, 2012, at 4:39 PM, tamouse mailing lists wrote:


I am thinking of limiting the colors to 10 for now (after all there
are only
so many ways to die a shirt. =)



Oh, please. There are lots more than 10 dyes in the world. Take a look
at a women's clothing catalog sometime or other...

Just look at this one t-shirt item alone:
http://store.americanapparel.net/2102.html

Individual item characteristics are going to be a lot different than
categories. You'll need expandable attributes for all kinds of things.
Colour is the obvious one here. Also: Size: not everything comes in S,
M, L, or is measured in that way. If this is for an apparel store that
sells a variety of different items, you'll need to solve this
generally across a whole lot of different types of clothing.

I'd really suggest you do a deep analysis of the different types of
items that are going to be sold, the attributes of each one, and
figure out how to best represent that breadth and depth.



Karl DeSaulniers
Design Drumm
http://designdrumm.com




--
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

Re: [PHP-DB] Storing multiple items in one MySQL field?

[B. Aerts]

Karl,

I'm somewhat limited to reading posts, so I might have missed something, 
but can you explain why you wanted to avoid the 3rd table solution ?


Because depending on that question, I can offer two other solutions, but 
they have their own limitations.


Solution 1: the 2,5nd table.
you create an extra table and write a procedure that makes a unique ID 
for every combination of attributes (and this procedure needs to be run 
every time you update/add attributes).


when you want to query items for a combination of attributes, you first 
select the attribute combination ID, then query your item list for all 
items with this specific ID.


It isn't exactly a 3rd table solution as it doesn't involve individual 
cross-references between items and a set of attributes, and thus saves 
you an INNER JOIN.



Solution 2: an alphanumeric bit-wise selection.
Instead of saving your combinations as binary number, you could extend 
it to, for example, a string of 4 characters per attribute: 4 characters 
times 26 characters makes 456976 possible combinations per attribute :


define MENS   =  ;
define WOMENS = AAAB ;

define GREEN  =  ;
define RED= AAAB ;

you write one PHP function that converts an array of combinations into 
an ISO wildcard filter (or does the MySQL syntax provide regular 
expressions ?), and apply this in a SELECT FROM ... WHERE ... LIKE 
statement.


$findTheseAttribs = new array ( MENS, GREEN );

$SQLlike=createSQLwildcard($findTheseAttribs); // returns 

$mySql = SELECT * FROM t_myTable WHERE itemAttribute LIKE  . $SQLlike;

The advantage is that you can assign different filter widths per 
attribute: MENS/WOMENS only need 1 character, size only needs 2 ( XXXS, 
XXS, XS, S, ... pretty limited), while colour can have op to 10 
characters to encode. This leaves reasonable room to expand/scale.


The drawback is that, when you add new attributes, your filter string 
expands, and the LIKE-statement might fail on the difference in string 
lengths.


Bert


On 12/01/12 02:18, Karl DeSaulniers wrote:

Yeah, I was being somewhat facetious about the colors of a shirt. :)
I agree on the items and attributes drill-down before implementation
though.
There will be more than T-Shirts. Watches, book-covers, etc, etc.

So I need to find a general logic to cover the items and attributes of
each item?
And this will make it scalable?

Best,

On Jan 11, 2012, at 4:39 PM, tamouse mailing lists wrote:


I am thinking of limiting the colors to 10 for now (after all there
are only
so many ways to die a shirt. =)


Oh, please. There are lots more than 10 dyes in the world. Take a look
at a women's clothing catalog sometime or other...

Just look at this one t-shirt item alone:
http://store.americanapparel.net/2102.html

Individual item characteristics are going to be a lot different than
categories. You'll need expandable attributes for all kinds of things.
Colour is the obvious one here. Also: Size: not everything comes in S,
M, L, or is measured in that way. If this is for an apparel store that
sells a variety of different items, you'll need to solve this
generally across a whole lot of different types of clothing.

I'd really suggest you do a deep analysis of the different types of
items that are going to be sold, the attributes of each one, and
figure out how to best represent that breadth and depth.


Karl DeSaulniers
Design Drumm
http://designdrumm.com




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Storing multiple items in one MySQL field?

[B. Aerts]

On 08/01/12 23:35, Karl DeSaulniers wrote:


On Jan 8, 2012, at 10:36 AM, Bastien wrote:




On 2012-01-08, at 7:27 AM, Niel Archer n...@chance.now wrote:



--
Niel Archer
niel.archer (at) blueyonder.co.uk

Hello phpers and sqlheads,
If you have a moment, I have a question.

INTRO:
I am trying to set up categories for a web site.
Each item can belong to more than one category.

IE: Mens, T-Shirts, Long Sleeve Shirts, etc.. etc..
(Sorry no fancy box drawing)

QUESTION:
My question is what would the best way be to store this in one MySQL
field and how would I read and write with PHP to that field?
I have thought of enum() but not on the forefront of what that
actually does and what it is best used for.
I just know its a type of field that can have multiple items in it.
Not sure if its what I need.

REASON:
I just want to be able to query the database with multiple category
ID's and it check this field and report back if that category is
present or if there are multiple present.
Maybe return as a list or an array? I would like to stay away from
creating multiple fields in my table for this.


Have you considered separate tables? Store the categories in one table
and use a third to store the item and category combination, one row per
item,category combo. This is a common pattern to manage such situations.


NOTE:
The categories are retrieved as a number FYI.

Any help/code would be greatly appreciated.
But a link does just fine for me.

Best Regards,

Karl DeSaulniers
Design Drumm
http://designdrumm.com

Hope your all enjoying your 2012!



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Neil's solution is the best. Storing a comma separated list will
involve using a LIKE search to find your categories. This will result
in a full table scan and will be slow when your tables get bigger.
Storing them in a join table as Neil suggested removes the need for a
like search an will be faster

Bastien
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Thanks guys for the responses. So.. what your saying if I understand
correctly.
Have the categories in one table all in separate fields.
Than have a the products table. Than have a third table that stores say
a product id
and all the individual categories for that product in that table as
separate fields associated with that product id?

Am I close? Sounds like a good situation, but I didn't want to really
create a new table.
One product will probably have no more than 3 combinations of
categories. So not sure it this is necessary.

EG:

Tshirts = 1
Jackets = 2
etc..

Mens = 12
Womens = 13

So lets say I want to find all the Mens Tshirts.. I was wanting one
field to hold the 1, 12

hope that clarifies

Karl DeSaulniers
Design Drumm
http://designdrumm.com



Hi Karl,

if you don't want to do with the third-table-solution, how about an 
assembler-style bit-wise OR of all categories ?


constant TSHIRTS = 1 ;  // 2 to the 0th power
constant JACKETS = 2 ;  // 2 to the 1st power
constant MENS= 8 ;  // 2 to the 3rd power
constant WOMENS  = 16 ; // 2 to the girl power :-)

INSERT INTO TABLE t_myTable ( ID, categoryField)
VALUES ( myNewId, TSHIRTS | MENS ) ;

SELECT ID
FROM t_myTable
WHERE  ( categoryField  ( TSHIRTS | MENS ))  0 ;

This assumes that your number of categories is not that big of course, 
as you're limited to 64 bits/categories on a modern machine.


Bert

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: PHP Delete confirmation

[B. Aerts]

You could try to reload the same page, but with an added GET-parameter 
delete:


I assume your snippet comes from a script called MAIN.PHP :

?php

if ( $_GET[delete] == yes ) {
   $id = $_GET[id] ;
   echo DIV ARE YOU SURE ? A HREF=delete.php?id=$idyes/A/DIV ;
}

while($row = mysql_fetch_array( $result )) {
   // rest of your snippet
   echo 'td'
   echo 'a href=MAIN.PHP?id='.$row['id'].'delete=yesDelete/a';
   echo '/td';
}

// and so on




If your code below doesn't detect the GET-parameter delete, it does 
what you have now.
If it does detect the parameter, you show a DIV with all formatting and 
buttons you want to confirm or abort the delete action.


I've left out some of the parameter checking for clarity.

Why would you want to avoid Javascript ?

Bert

On 29/04/11 06:20, Chris Stinemetz wrote:

I have been trying to figure out how to add delete confirmation for
the bellow snippet of code. I would prefer not to use javascript. Can
anyone offer any advise on how to right the delete confirmation in
PHP?

Thank you in advance.

P.S. I apologize for the indention. For some reason gmail messes it up.

?php
 // loop through results of database query, displaying them in the table
 while($row = mysql_fetch_array( $result )) {

 // echo out the contents of each row into a table
 echo tr;
echo 'td' . $row['Name'] .'/td';
echo 'td' . $row['Date'] .'/td';

echo 'td' . $row['StoreInfo'] .'/td';
echo 'td' . $row['Address'] .'/td';
echo 'td' . $row['Type'] .'/td';
echo 'td' . $row['EngTech'] .'/td';
echo 'td' . $row['StoreManager'] .'/td';
echo 'td' . $row['BBtime'] .'/td';
echo 'td' . $row['BBup'] .'/td';
echo 'td' . $row['BBdown'] .'/td';
echo 'td' . $row['SiteSect'] .'/td';
echo 'td' . $row['VoiceCall'] .'/td';
echo 'td' . $row['Comments'] .'/td';
 echo 'tda href=edit.php?id=' . $row['id']
.'Edit/a/td';
 echo 'tda href=delete.php?id=' . $row['id']
.'Delete/a/td';
 echo /tr;
 }

 // close table
 echo /table;
?



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Mac OS X 10.5 : odbc_connect( ) error code IM002 problem with MySQL

[B. Aerts]

Hello,

I've already spent quite some time trying to solve ths one, hopefully 
some-one here can lend me a hand.


I'm trying to set up a LAMP configuration, but on a Mac (so actually a 
MAMP ...). I have a MacBook Pro, Mac OS X 10.5.5, built-in Apache server 
and built-in PHP module ( phpinfo says it is PHP 5.2.6 on Darwin kernel 
9.5.0). MySQL is the 5.1 Community version.


I want to use the ODBC connection in order to clearly separate data from UI.
So I've set up an ODBC connection (System DSN called THUIS) using the 
Mac ODBC Administrator, and tested the link - works fine ( did the ODBC 
test with trace, worked, trace file yielded all zero's ).

I connected to the database using OpenOffice Base through ODBC - works fine.

Then I tried a simple PHP-script - and this fails :

Warning: odbc_connect() [function.odbc-connect]: SQL error: 
[iODBC][Driver Manager]Data source name not found and no default driver 
specified. Driver could not be loaded, SQL state IM002 in SQLConnect in 
/Users/bertaerts/Documents/Apache-PHP/DB_thuis/connect.php on line 6

Could not connect to ODBC source THUIS

The script is :
== start of script =
?php// connect to database

putenv(ODBCINI=/Library/ODBC/odbc.ini);

phpinfo() ;
//$cnx = odbc_connect( 'THUIS' , 'root', '**' ) or die(Could 
not connect to ODBC source THUIS);


$cnx = odbc_connect(DRIVER={MySQL ODBC 5.1 
Driver};DATABASE=thuis;SERVER=localhost,root,**) or 
die(Could not connect to ODBC source THUIS);


?
 end of script =

Note: I've first tried the commented line of code, but that yielded a 
different error, IM003 ( Warning: odbc_connect() 
[function.odbc-connect]: SQL error: [iODBC][Driver Manager]Specified 
driver could not be loaded, SQL state IM003 in SQLConnect in 
/Users/bertaerts/Documents/Apache-PHP/DB_thuis/connect.php on line 4

Could not connect to ODBC source THUIS )

Surfing the Internet didn't return anything close to this configuration, 
or this problem.


Any-one who has a clue of what is going wrong here, or to what I'm doing 
wrong ?


Thanks in advance,

Bert.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php