solution-digest

From: DB Fiend <dbfiend@hotmail.com> Date: Sun, 29 Oct 2000 23:54:23 -0500 Subject: view table setup? > >What's the command to view a tables column names, field tapes, sizes? > >dbfiend RTFM :) -o- >Hi, > >I'm not sure whether I have understand your question. But I think you >can set a large enough field in "Contact" talbe, and call it "cat". Put >all the category-ids of a contact into this field, and seperate them by >comma. In your query string, you use >"select * from contact where cat like \"%<the id>%\"". > >Hope this can help. > >Alex > > >> Hello, >> >> I'm using PHP with MySQL on UNIX box running Apache and I need some help >> relating two tables... >> >> I have a table called Contact which holds records with standard contact >> info (ContactID [unique], Name, Add1, Add2, State, Country, Zip, URL). >> ALMOST good. however there is a point where this method goes wrong, you have to include commas at the beginning and the end of the stored string, and search for "%,<id>,%" <- read on, I'll explain. I think you can use table joins or whatever they are called to solve your problem, but I do not recommend it due to speed problems. But, here are two easy solutions: first, select from CategoryContact all the rows you need, for e.g.: $result = mysql_query ("select id from CategoryContact where CategoryID=$category_to_select"); // we only need the id then, run through the results, and for each result select the appropriate row, for e.g.: $x = 0; while ($row = mysql_fetch_array ($result)) { $id = $row["id"]; $re2 = mysql_query ("select * from Contact where id=$id"); echo "(here goes whatever echoing or processing you need, you can store values in an array for .e.g, just use $x for indexing)"; $x++; }; the $x should only be used if the no. of the current result is relevant, which will only occur if you want to store results in a row. However, if it is not too late to modify thing, I suggest another solution: use a char array for storing categories INSIDE the Contact table. Then, store categories like this: ",12,31,52,194,21,42,122," why is this good ? You can simply choose the qualifying contacts like this: "select * from contact where category_code like \"%,31,%\""); and, you can easily process these codes with explode, for e.g., you can easily join categories with ",".$num1."," etc, you can easily eliminate any of the numbers by cutting out ",$num" (so the , s remain correct). And, why to include commas in the beginning and also in the end? If You would not, "%,12,%" would not match the value above. You should have to use "%12%" BUT this would also match the value 122, for e.g. With this method, You can eliminate some sql lookups which speeds things up a big bit. :) Of course, you will still need the categories table to define which number means what. From: "Vojtech Dvorak" <vojtech_dvorak@servind.cz> Date: Mon, 30 Oct 2000 11:18:47 +0100 Subject: regularly mysql db web update ? for your problem, I can imagine the following: the server which will save the backup, has a script that runs hourly, connects to the server which actually hosts the database to backup. Let's call these servers main (which normally runs) and bck (which will store backup). So main will have a script which outputs all the stored data to a web page, for backup server, and backup reads this, analyzes it, and then updates its own database appropriately. There are many problems with this. - with a large database, each backing up will take quite a few time. - you can do not much with your backup if anything goes wrong. - you have to solve authentication (otherwise everyone can read your data), and above this, anyone can read the transmitted data unless you use https. https uses another port. For authentication, host authentication is not enough since it can be spoofed. you can zip your data and send it back, instead of dumping data to a html page. you can even password-protect your zip, and have the receiving php script do the unzipping (or, it will only be used as a back up, you only have to store it). this is all theoretical and very very very messy to get working. however, I strongly suggest that you have your admin open an ssh port for you on the firewall and change to a provider which lets you log in via ssh.