[[SOLVED] INSERT......ON DUPLICATE question]

Will do. Thanks!

[[SOLVED] INSERT......ON DUPLICATE question]

I think I like the sound of the duplicate key more (I know how to do them!)

 

Which is 'better' from a database point of view?

[[SOLVED] INSERT......ON DUPLICATE question]

Triggers... look promising. Can you give me a quick pointer please? I'm reading the documentation now but finding it a bit confusing.

[[SOLVED] INSERT......ON DUPLICATE question]

Hi,

 

I'm having trouble getting my head around INSERT...... ON DUPLICATE at the moment.

 

I have three tables,

 

Product details:

id, description.....

 

Categories

type, value

 

Product Categories

product_id, category_value

 

Example data for the Categories table:

height, short

height, tall

colour, red

colour, blue.

 

What I want to do is have no more than one entry for each category 'type' for each product (i.e. a product can be tall and red but not tall and short).

 

I was hoping to do this with INSERT...ON DUPLICATE as this means when a user changes a product from red to blue I don't have to do "if colour exists delete colour insert new colour".

 

Thanks for any and all help!

[Cleaning input]

Oooh I like the look of that. Thanks. End of question.. for now.

[Cleaning input]

Ok, so looking through mysqli it looks much nicer (liking the OO style). Yet another question though, do I need to run ALL queries through Bind or do I just do that with queries with user input in them?

[Cleaning input]

Hi,

 

Sorry to keep asking questions, but I'm new to this.

 

Do I need to use an abstraction layer? Are they more designed for projects that could be run on ANY system rather than a specific setup?

[Cleaning input]

Ok, so I'm writing a new system and have control over the server so I guess MySQLi is the way to go.

 

My next question is, is it worth me writing a database abstraction layer? Is MySQLi easier to just use directly?

[Cleaning input]

So if I use mysqli I don't need to 'clean' the data at all?

 

If I'm writing a new system, should I use mysqli?

[Apache, SSL and PHP port issues]

Hi,

 

I've managed to setup my debian box with apache, php and ssl but it's behaving a bit strangely.

 

First of all I don't get the $_SERVER['HTTPS'] variable (not shown on print_r($_SERVER) and the port that the system accesses it on is strange.

 

If I type in "http://myurl.co.uk" it works fine, $_SERVER says I'm on port 80 just right. If I then type in https://myurl.co.uk it STILL says it's on port 80. I have to type https://myurl.co.uk:443 to make it go to 443 and then it rewrites the URL as https://myurl.co.uk (i.e. it drops the port number).

 

This is obviously really annoying as it means I need to  make all my links :443 from the page to make it work properly. Why is this happening?

 

Thanks

 

Andrew.

[updating mysql database with fields in loop]

I haven't had time to look closely at the code, but try

echo $qry['comment']; 

instead of

echo "$qry[comment]"; 

 

Do you get any error messages?

[Cleaning input]

If I were to write a function to get user input such as

function get_input($name)
{
  if(isset($_POST[$name]))
      return mysql_real_escape($_POST[$name]);
  else
      return false;
}

And then collect all user input from this function (never directly) then I don't need to 'clean' any of my MySQL queries after that?

 

I suppose what I'm asking is is there any other way of injection attacks (etc) happening?

 

Thanks

 

Andrew.

[[SOLVED] 'Intersect' style query]

You may want to read this thread.

 

SELECT pc.product
FROM pc as t1
CROSS JOIN pc as t2 USING ( id )
WHERE pc.category = 'plastic' and pc.category = 'tall'

 

Obviously, if you don't have an id field, you should use USING ( product, category ) -- and have a combined index.

 

Hope that helps.

Humm... you say 'obviously'.... I don't really understand multi-field keys. Can you elaborate a bit please?

[[SOLVED] 'Intersect' style query]

Oooohh I like the look of the JOIN one. I will try that.

 

Thanks a lot

[[SOLVED] How best to structure this table?]

What a great answer! Thank you!!

 

Do you by any chance know what program generated that diagram? I've been using Visio but that diagram is MUCH better.

[[SOLVED] 'Intersect' style query]

Is that more efficient than doing

SELECT product FROM product_table WHERE category = 'tall' IN (SELECT product FROM product_table WHERE category = 'plastic');?

[[SOLVED] How best to structure this table?]

Hi,

 

I want to save information about products in a table. Each product can have up to 11 different 'features' and each 'feature' requires about 8 fields to record the data on that particular feature.

 

This is obviously going to be quite a large table with 11x8 fields in the end off it, most of which wouldn't get used that often (normally only 3 or 4 features are used).

 

Would I be better putting the features in a seperate table with 9 fields in, 8 to record the features and 1 to record the product code?

 

If so, can I make a 'view' to show me the table as if it were one huge table with each feature in a column after the other?

 

Thanks.

[[SOLVED] 'Intersect' style query]

Wont that simply give me all the products that are EITHER tall or plastic? I want products which are BOTH tall AND plastic.

 

e.g. if I were to search for a tall AND blue product I only want to find 123 but if I search for a tall AND plastic product I want ABC AND 123.

 

Thanks.

[[SOLVED] 'Intersect' style query]

Hi,

 

I have a table used for indexing categories of products like:

Product | Category
--------|---------
ABC      | Plastic
ABC      | Red
ABC      | Tall
123      | Plastic
123      | Blue
123      | Tall

 

Now, how can I select all the tall & plastic products? This table could grow quite large so I want to keep this as efficient as possible.

 

I thought that some kind of intersect query would work, but mySQL doesn't have them apparently

 

One more complication to throw in is that I don't know how many categories there will be. I may need to search for one or five or.....

 

 

 

Any and all help much appreciated.

 

Thanks.

 

Andrew.

[Data from HTML file, regex?]

Hi,

 

I have an HTML file with the code

<input type="hidden" id="slotprod_1" value="WV013">

and I want to extract the value "WV013" from it (without the quotes). This is contained on a page with lots of other HTML.

 

Is a regular expression the way to do this?

 

Thanks

 

Andrew.

[Combining data from multiple tables]

Thanks for confirming.

 

What do you mean by type of database?

[Combining data from multiple tables]

Ah I see, so something like

SELECT * FROM Sellable s, Components c WHERE s.ProductID = 'Value' AND s.ProductID = c.SellableID

 

Do I understand correctly?

[Combining data from multiple tables]

Hi,

 

I'm making a stock control system and want some advice with the best way to do things.

 

Each of our products that we sell is actually made up of other components that we buy. I want to store the details for the components and for the piece that we sell, but there is no guarantee of how many pieces make a product.

 

I was thinking of storing them:

Sellable:

=============

Product ID

Description....

blah

blah

=============

 

Components

=============

Comonent ID

Sellable ID

Blah

Blah

=============

 

Which will work obviously, but I'm not sure what the best way to output the combined data would be. Would I need to use a JOIN? A "References"? (what does that do??)

 

Any and all help would be great. Thanks.

 

Andrew.

[Apache maxclients problem]

My httpd.conf file is empty. All my settings seem to be in apache2.conf. Is this bad?

 

My keepalive is currently turned off.

 

How do I turn on "server-status" and where do I look at these dots?

 

Sorry for so many questions, I have a lot to learn!

 

Andrew.

[prevent ppl from viewing contents of some directories]

I think you need to "Allow all" and then add "Options -Indexes" which will stop the folder listing files (is that what you were after??)

 

Or, if you don't want to write an htaccess file you could always put a blank index.htm file in there instead.