Planetarion Forums - php / mysql-question

wu_trax · 6 Jun 2003, 18:29

once again i dont get along

atm im writing a little web-application to store some information about my movie-collection.
now i want to put a small jpg-file (i.e. the cover) into the database (putting all those files in the file-system is a little confusing)
if i upload it using phpmyadmin it works fine.
if i then export the data out of the database, the data loos something like this "0xffd8ffe000...", so i guess thats binary.
there is no ' ' around that part in the insert statement.

so, now i wrote a small php-script to put the files i already have on the filesystem into the database:

Code:

$dir = opendir("cover");

while($filename = readdir($dir)){
	$pos = strpos($filename, ".");
	$number = substr($filename, 0, $pos);
	if(is_numeric($number)){
		$handle = fopen ("cover/" . $filename, "r");
		$cover = fread ($handle, filesize ("cover/" . $filename));
		

		$sql = "UPDATE movies set cover = $cover WHERE number=$number";
		mysql_query($sql, $connection);
		if(mysql_error() != "")
			echo(mysql_error());
		
		fclose ($handle);
	}
}
closedir($dir);

obviously this doesnt work

now im wondering why.
how do i get that file into the database?
PS file_get_contents() doesnt work neither

PPS fopen ("cover/" . $filename, "rb") doesnt change anything, its a linux system anyway

PPPS the other way round, i.e. echoing the contens of $row['cover'] works fine.

queball · 6 Jun 2003, 18:48

Code:

$sql = "UPDATE movies set cover='" . mysql_escape_string($cover) . "' WHERE number=$number";

[pimp]

Code:

mysql_formatted_query("UPDATE movies set cover=% WHERE number=%", $cover, $number);

[/pimp]

wu_trax · 6 Jun 2003, 18:51

thanks a lot, that seems to work.
im too dumb for this language

NEWSBOT3 · 12 Jun 2003, 03:49

Quote:

Originally posted by queball

Code:

$sql = "UPDATE movies set cover='" . mysql_escape_string($cover) . "' WHERE number=$number";

[pimp]

Code:

mysql_formatted_query("UPDATE movies set cover=% WHERE number=%", $cover, $number);

[/pimp]

queball, you fking rock.

MT · 13 Jun 2003, 17:15

Quote:

Originally posted by queball

Code:

$sql = "UPDATE movies set cover='" . mysql_escape_string($cover) . "' WHERE number=$number";

[pimp]

Code:

mysql_formatted_query("UPDATE movies set cover=% WHERE number=%", $cover, $number);

[/pimp]

Just a slight issue, the return statement in mysql_formatted_query [recap]

Code:

return mysql_query($query) or die("mysql_formated_query: ".mysql_error()." in ".$query);

will evaluate the mysql resource to either true or false, and returns a boolean value.
This occurs at least in my setup. Changing it for

Code:

  $t = mysql_query($query);
  if (!$t) {
    die("mysql_formated_query: ".mysql_error()." in ".$query);
  }
  return $t;

fixes the problem. As I say, YMMV, this is just what I have encountered. Otherwise, a very nicely thought out function quey, normally I do them longhand, but I can't see any flaws in this one, so I'm going to test drive it for a few weeks.

queball · 13 Jun 2003, 17:23

woopsie!

ta

Dante Hicks · 14 Jun 2003, 18:30

Excuse me for general stupidity, but is queball's script enough for all kinds of text input?

For instance, I've got a textbox for inputing (say) a movie review. I then assign that text to $ArticleText via a POST form.

What do I have to do with $ArticleText to make it Preview properly, insert into MySQL, and then (finally) be pulled back correctly from MySQL when being displayed as part of a general "Print" statement? Will escape_string be enough to save me from the evils of HTML tags, single quotes and double quotes?

The text box would only be for text input by me, so security isn't a concern if that matters.

queball · 14 Jun 2003, 18:48

Nope, it's only quotes for SQL. And it wouldn't let you use % symbols in your SQL easily, for instance in a LIKE expression.

Always have it in literal format internally. Quote using escape_string for SQL, quote using htmlentities for HTML.

wu_trax · 21 Jun 2003, 20:05

yet another php-question (not in any way related to the other question):
is there any way to define variables that exist only once in the whole application? (like the attributes you can define for a java-servlet, that exist as long as the webserver is running and keep their values)
i dont want to do god knows how many DB-queries every time the page is loaded, i want to read the data once and then store it somewhere

hell, i dont get along with this language

queball · 21 Jun 2003, 21:55

wu_trax: you could mess with shm_attach etc, you have to be careful serialising your accesses and so forth. It's like using a file and if you used a file your data would stay alive when the server goes down. I might use shm for cached data.

wu_trax · 21 Jun 2003, 23:01

thanks again queball

ill have a look at this shm, it looks a little complicated.
ill try to explain my problem a little better:
there is this product database for some website.
i need this data quite often and its not that easy to get (i.e. i need a few queries to get the data)
so i thought it wouldnt be such a bad idea to put that data (its not that large, although it might be a few kb) in some array instead of accessing the db everytime the webside is reloaded.
i could serialize that array and put it into the session (i think, i dont know how much data i can put into a session) but that would still mean db-accesses every time a new session is started.

Dante Hicks · 21 Jun 2003, 23:03

Quote:

Originally posted by wu_trax
i need this data quite often and its not that easy to get (i.e. i need a few queries to get the data)

This is really crude, but couldn't you just dump the info into either a plain-text file or a temporary table (just the one query then)?

pablissimo · 21 Jun 2003, 23:28

Quote:

Originally posted by Dante Hicks
This is really crude, but couldn't you just dump the info into either a plain-text file or a temporary table (just the one query then)?

You could expand it a little with some kind of table recording boolean flags for modifications on other tables since the last cache and update the temporary cached copy only when required. Though I would suspect the actual time in doing the appropriate queries to find out whether you need to do some more would be similar to just doing the queries in the first place in many situations.

wu_trax · 21 Jun 2003, 23:30

hmm, wouldnt a text-file be even slower than the db-access?
i cant put the data into one table, because its all together a bit complicated, there are some products with only one price and other products that have 3 different prices (or even more, there should be no restrictions)
however, i cant put these products into different tables, because the customer wants to be able to combined all products to new, other products.
hell, i cant even explain the problem

.
not to talk about generating some multi-dimensional-array in a language i dont get along with to solve a problem i dont understand with as few db-accesses as possible and then storing said array in a way that is easy to understand once i have left the company i currently work for and still globally accessable.

at least i understand now why my coworker never finished that project.

pablissimo · 21 Jun 2003, 23:37

What the hell product has three prices? =)

wu_trax · 21 Jun 2003, 23:48

its a freakin pizza-service (size m, l and xl

). i dont even want to think of working for a real company

however, as i said, i cant put in three diffrent prices because there are also other products which only have one price (and size), like drinks or the ingredities of the pizza.
so i made another table called priceclass, linked that table with the product table and put the price into the relationship (which obviously was a crap idea)
maybe i should just make different tables for all product groups and tell the customer to GO TO HELL if he wants to add other product groups

queball · 21 Jun 2003, 23:51

Quote:

Originally posted by wu_trax

The filesystem is cached, and is fast. But a recent MySQL server with query cache is fast too.

I doubt the speed difference is important - write the easiest code possible then profile to see if this kind of optimisation is important. If you actually have a design criteria to use the fewest SQL queries possible you should ignore it IMO. Speed is speed.

wu_trax · 21 Jun 2003, 23:57

i thought the speed would become a problem because of the many queries i need.
i guess ill try to finish it the way t is now and see if the performance is really as horrible as i expect it to be.
thanks anyway.

pablissimo · 22 Jun 2003, 00:12

Quote:

Originally posted by wu_trax
its a freakin pizza-service (size m, l and xl ). i dont even want to think of working for a real company
however, as i said, i cant put in three diffrent prices because there are also other products which only have one price (and size), like drinks or the ingredities of the pizza.

Can you not just throw a ProductCategory field in? Such as

Example: ProductID, ProductCategory, ProductSize, ProductPrice

I realise this is oversimplified given I dunno the exact circumstances, but then you could just ignore ProductSize for things like portions of extra cheese etc based on Category.

Mweh, I was **** at DB design.

Edit:
I just reread that and realised it's actually got nothing to do with the topic heh \o/

wu_trax · 22 Jun 2003, 00:23

yes, but in that case i have 3 different products where there is only one product. ive got a table that links products with other prodicts (ingredities) aswell

, this means i have to store that relationship 3 times (and update it 3 times once it has changed, its all horrible

i guess ill keep it as it is, finish it and then see what happens.