dino2dy

Thanks a bunch once again

Thanks a lot mate it worked. I really don't know all the uses of the join queries i mean I know what they do but I always thought their main use was to just create a cooler table where people can see everything from different tables but thats kinda stupid when I think about, but the whole creating a whole new field as an average of two tables using join thats some mighty cool shit, don't even have to store it or anything. Well it's pretty much done (the application) so if you ever need anything (not via mysql or php cos you obviously rank a couple of levels higher than me on that, unless you need a ; checker or something) By the way any chance I could get something to contact you so I could ask you questions in the future (it's a thin line between engaging and obnoxious isn't it)???

Well in the table actors I have different actors and in the table movies I have different movies and on the web page I created a drop down list which shows the Actor Name but inserts the actor ID into the movie table and so in the movie table every movie really has one actor (I just got what was so confusing, a movie in this case can only have one actor cos it's not really a movie, it's a client and the other thing is project and only one client per project, and then for another project there can be another client but not for the same project). Ok that explanation sucked let me try the real thing. The company i'm doing this for is a big company that makes everything from electrical power plants to buildings. And it wants a sort of CMS to take care of its Tenders (I think thats how u say it, might be Public Procurements or somethin). And each Tender is given by a Komitent, or a Bosnian word for a company or client that is offering you the Tender to do (not only you but several other companies and the one that comes up with the best offer gets the contract), it is a singular tender to a singular Komitent (client) (of course a tender can have "lotovi" but thats a different thing and theres a tenderlot table for that). Anyhoo I hope this explains it a bit better. As for your solution, the code there is php code that takes the query $query = "SELECT NazivKom, KomitentID, Adresa, Telefon, E_mail, PostanskiBroj, MatBrKom, PorBrKom FROM komitenti ORDER BY NazivKom ASC"; and then turns it into an array and prints out the results in neat little rows, as well as printing out a table (green, yay!!!) for those rows. So how would I implement your code on this select query and would I then be able to use the php in the previous message to get the rows (would there be an avg thing specified in the array when fetching it, I mean would the array get the dynamically created column average as any other column (NazivKom, Adresa...) or would I have to do something differently In order to be able to show it on the web page??? Once again thanks a lot for answering, usually I don't get many answers on my posts.

No offense taken, not so long ago I would have said no but yeah they are used to join different tables in a database in other words create a bigger table using a column which exists in both as a sort of reference. And as for the many to many thing, I dunno, haven't had any problems yet, I just made the actorID in the movie table a foreign key, but as I said the database was given to me I didn't create it. As for the avg you got it, and I got you, I don't need to store the values I'll just show em, but I'm afraid I won't be able to show em in a table (so it looks pretty), cos now I'm showing all the key values in a table as columns, but I don't know maybe you can even if you do it dynamically. Here's how i show the columns while ($row = mysql_fetch_array( $result )) { // build table to display results print( "<tr style= border-color:#00FF00;>" ); print( "<td style= border-color:#00FF00>"); print ($row['NazivKom']); print( "<td style= border-color:#00FF00>"); print ($row['Adresa']); print( "<td style= border-color:#00FF00>"); print ($row['Telefon']); print( "<td style= border-color:#00FF00>"); print ($row['E_mail']); print( "<td style= border-color:#00FF00>"); print ($row['MatBrKom']); print( "<td style= border-color:#00FF00>"); print ($row['PorBrKom']); print( "<td style= border-color:#00FF00>"); print ($row['PostanskiBroj']); print("</td><td style= border-color:#00FF00;> <a href=\"komitenti.php"); print("\"> Dodaj</a></td>"); print("</td><td style= border-color:#00FF00;> <a href=\"EditKomitent2.php?KomitentID="); print($row[1]); print("\"> Promijeni</a></td> <td style= border-color:#00FF00;><a href=\"?dio=politika&brisi=OK&id="); print($row[1]); print("\">Izbrisi</a></td>"); print( "</tr>" ); } // end for Hope this makes it kinda clearer, and thanks for your help.

First, thanks for the reply. I'm not sure I really understand you though. You say I don't need the avg rating column in the actors table but surely without it how am I supposed to store the avg rating (it has to go into a column) and by storing it automatically (which was the idea not me typing it in every time) they can then see it along with all the other columns. $query = "SELECT NazivKom, KomitentID, Adresa, Telefon, E_mail, PostanskiBroj, MatBrKom, PorBrKom FROM komitenti ORDER BY NazivKom ASC"; This is my Query for showing the actors. I would implement your query here into this query? But even so isn't it important to store the actor rating for each actor? I mean that way people know which actors to avoid and which to see. Your way, if I got it right, would show them here the actor rating, but wouldn't store it in the database, then again can't right now think of a reason why it should. As for the many to many, the database was given to me by a company as a failed project and the company, not having time to introduce me to their business just told me to work using that database. The database they gave me sucks on an epic scale (and I'm new to this but even I could see it) but I don't wanna anger the Gods by changing it too much (I mean they have columns there they are never ever going to need, 15 bloody tables for something that should have been done in 6,7). Anyhoo if you could kinda explain to me better how to implement your query with my select I'd really appreciate it.

I wonder if you could help me with a problem, I have a Rating system, which rates movies (not really but its easier to explain using this example and its exactly the same). So i have a form for inserting a movie, and in it I have a drop down list choosing 1-5 as a rating of the movie. Now I also have another table Actors, and a form for inserting Actors. The tables are connected via ActorID which is a foreign key in table Movies. Now I want to somehow automatically insert into table Actors an Average Rating (which is a column I have in the table, but its not a field in the form and can't be inserted by a user on the webpage) for the Actor, which would be an average of the ratings his movies have got. So when inserting a movie if you choose that actor it automatically inserts his ID into the table Movies and the rating for that particular movie, and somehow I would like to automatically calculate for every actor his rating. The code I have so far goes a bit like this (I'm on a computer with no Dreamweaver so am copying this from notepad so pardon the syntax mistakes) <?php $query=SELECT KomitentID, AVG (tenderi.RatingTendera) FROM komitenti, tenderi WHERE komitenti.KomitentID=tenderi.KomitentID while ($prosjek=mysql_fetch_array($query)){ $sql=INSERT INTO komitenti.ProsjecniRatingKomitenta VALUES $prosjek; ?> It's quite bad I'm sure, and I have no idea where to even insert it. Cos I have 2 files I use for inserting, one as a form, and the other is a php file that the form is transferred to (action=...) and thats for inserting a movie, and the same for inserting an actor, so 4 files really. If somebody can shed some light on the issue, I would wish him good women and good ale for the rest of his days.

Damn i must have missed it. Could have saved myself a lot of bother, but the stupidity is strong with me. If I could bother you on a different problem, I have 2 tables, Movies and Actors. And in one table i have a column called Movie Rating (not really but the names are in my language and it's easier to get this way) and in the other table i have AverageActorRating. Now i have a INSERT INTO query for the MovieRating column. It's a simple drop down list consisting of numbers 1-5. Now this works and it inserts it into the database as an int. There is an ActorID column in both Movies and Actors table. Now what i wanna do is insert into AverageActorRating an average of all the Ratings he's gotten so far for the movies in Movies he's been in (where the ActorID is the same). Now I have some code as to how i was thinking of doing it, but I haven't tried it out yet (been doing the expiry date thing), but since I have the expert here I thought why not ask you if my code is any good, and if there is some easier way (like with the expiry date) to do it. Here's the code so far <?php $query=SELECT KomitentID, AVG (tenderi.RatingTendera) FROM komitenti,tenderi WHERE komitenti.KomitentID=tenderi.KomitentID while ($prosjek=mysql_fetch_array($query)){ $sql=INSERT INTO komitenti.ProsjecniRatingKomitenta VALUES $prosjek; ?> I copied this from notepad cos I don't have dreamweaver on this computer so there are probably some syntax mistakes for which i apologise profusely.

Actually Rick James helped me (I wonder if he's the guy Dave Chappele mocks). This hides expired items from the user: SELECT ... FROM tbl WHERE exp_date > NOW(); This removes all the expired items in one statement -- no loop: DELETE FROM tbl WHERE exp_date < NOW(); I tried it out it works, the only thing is it doesn't show files which expire today, but very simple solution. (when i think i wrote a page of php... sad)

Ok here is my attempt to make this work, but while it doesn't give me any error, it won't delete the Ponude where the OpcijaPonude is smaller than the current date. It doesn't give me any error, and the if statements seem to work, because if i play around with echo instead of the sql statement it echoes the date as a date. <?php $query="SELECT OpcijaPonude FROM tenderlotponude"; // Connect to MySQL if ( !( $database = mysql_connect( "localhost", "root", "pokemon" ) ) ) die( "Could not connect to database </body></html>" ); // open Products database if ( !mysql_select_db( "dino", $database ) ) die( "Could not open register database </body></html>" ); if ( !( $result = mysql_query( $query, $database ) ) ) { print( "Could not execute query! <br />" ); die( mysql_error() . "</body></html>" ); } // end if while ($row = mysql_fetch_array( $result )) { $opcijaponude=strtotime ($row['OpcijaPonude']); $currentdate =strtotime("now"); if ($currentdate > $opcijaponude){ $expiry= date ("Y-m-d", $opcijaponude); $sql="DELETE FROM tenderlotponude WHERE OpcijaPonude = $expiry"; mysql_query($sql) or die ("Pojavila se slijedeæa greška pri upisu podataka, molim prijavite je!<br /><strong>".mysql_error()."</strong>"); } } mysql_close( $database ); ?> I hope you guys can tell me what I'm doing wrong. Also mine is date time as well and don't know if that's a problem. And it's a small database so how would I do it if I wanted it to go through every time the page loads? I did the include timestamp.php; in a page that lists all of the rows in the table tenderlotponude, so every time somebody enters that page it should delete the things that are expired. Resources really aren't an issue at this point so is that the way to do it? Thanks a lot for all your continued help

Hello guys once again. Hope some of you are still looking through code at the forums, instead of looking at women at the beaches. Fingers crossed. Let's cut to the chase. I have a database table with a field expiration date which is filled using a javascript calendar and which fills a form on a form.php page, which then sends it to a fillform.php page where the values from the form are inserted into the database using the INSERT INTO ... VALUES... '$_POST[Exp_Date]'. Now This is all well and good and it works inserting it into the database but what I want to do is make a sort of code that will automatically delete the whole row, or make it invisible somehow, if the expiration date is > than today's date. I know that this code echo date("Y-m-d") writes the today's date. So my thinking is to do something like while ($exp_date=$_POST['Exp_date']) if $exp_date>date $expired=$exp_date $query=Delete from... Where ['Exp_date'] $expired The thing is though it's supposed to run automatically through the database and check if something expired so it could delete it. I'm not sure really how to do it. So if u guys have any suggestions, I'd really appreciate em. Thanks for your continued help, I'd be nowhere without you.

I created a page that lists all the rows from my database in a list in a web page. Next to each of the rows are edit and delete buttons. I want the user to be able to edit the row for which the edit button is clicked. The web page has no login so no session is started, I had looked for awhile around the web, and saw that there might be a way to store the ID from the row into a cookie but im not sure how to do that. I just can't figure out how to save the ID of a particular row that is clicked on and then use it to identify the row that needs to be edited or deleted. Here's the php code for the page that prints out all the rows from a database and the edit buttons next to em. <?php $query = "SELECT * FROM komitenti"; if ( !( $database = mysql_connect( "localhost", "root", "pokemon" ) ) ) die( "Could not connect to database </body></html>" ); // open Products database if ( !mysql_select_db( "dino", $database ) ) die( "Could not open register database </body></html>" ); if ( !( $result = mysql_query( $query, $database ) ) ) { print( "Could not execute query! <br />" ); die( mysql_error() . "</body></html>" ); } // end if // fetch each record in result set for ( $counter = 0; $row = mysql_fetch_row( $result ); $counter++ ) { // build table to display results print( "<tr style= border-color:#00FF00;>" ); foreach ( $row as $key => $value ) print( "<td style= border-color:#00FF00>$value </td><td style= border-color:#00FF00;><a href=\"EditKomitent2.php\">EDIT</a></td>"); print( "</tr>" ); } // end for mysql_close( $database ); ?><!-- end PHP script --> </td> I already created a web page with a form for inserting a new row which works, and I figure I can use the same form for editing an already existing rows as all the fields are the same Ill just save it under a different name change the SQL from Insert Into to Update and link to it. But how do I identify the row it's supposed to edit? I am really at my wits end, I tried to do it myself failed, tried searching online but all of the things I ran into were so complicated. I tried accomodating some stuff to suit the online things but I couldn't do it. So any help would be really appreciated.

Yeh they're in the same order (the enum values im guessing u mean) cos i copied and pasted them from the StatusID column to the Status column. Also the StatusId column has records, its not empty, i created just 2 for testing purposes, one ponudjeni for some reason works, but any other that i create doesn't. Hope someone figures it out.

The sql dump sucked it was old and i didnt check it so ignore it, let me tell u a few things, both tables are InnoDB, both are of the same values unlike the sql dump so both enum with the same values, tried makin em both unique or both indexes, tried makin one a primary key and te other an index, all failed, and the most common reason is the foreign key constraint. So after searchin for the past couple of hours on some forums, i used the showInnoDB status thing to get the following message CONSTRAINT `FK_tenderi_status` FOREIGN KEY (`Status`) REFERENCES `statusi` (`StatusID`) Trying to add in child table, in index `IndexStatus` tuple: DATA TUPLE: 2 fields; 0: len 1; hex 00; asc ;; 1: len 2; hex 002e; asc .;; But in parent table `dino`.'statusi', in index `IndexStatusID`, the closest match we can find is record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 01; asc ;; 1: len 6; hex 00000000060b; asc ;; 2: So if anyone has any ideas i would love them forever, because i searched everywherer using this error and couldn't find anything, so PLEASE PLEASE PLEASE HELP

All right i didn't even bother searchin for this topic cos im a 100% certain no one has ever had this problem. Im designing a database to take care of tenders for a company, which told me to do it according to an erd they created (which sucks) so i did. By the way im not getting paid for this, its a school thing. So in this database we have a table tenderi and a table statuses which are 5 different statuses a tender can be in like not offering for this tender, offer made, contract made etc. So i made this web page form where u can insert a tender and where u choose a status from a drop down list which is gathered from the table statuses whose primary key, statusID is a name of the status since there are only 5 of em. Status ID is an enum value with 5 possible values. Ok so the mistake im getting is when i try to insert a tender. It tells me: Could not execute query! Cannot add or update a child row: a foreign key constraint fails (`dino`.`tenderi`, CONSTRAINT `tenderi_ibfk_4` FOREIGN KEY (`Status`) REFERENCES `status` (`StatusID`)) So far so familiar u think right? Well here's where it gets weird, if from the drop down list i choose a value of status called 'offered' it works, it inputs it into the database like everything is hunky dory, but if i choose any other value it gives me that mistake, I even tried deleting the table statuses and building it again, still the same, when i changed the table offered to be something else like tender offered it didnt work, for some reason it works only on the offered but i have no idea why, as its not specified as anywhere in my code which is like this for statuses (i did most of it in phpmyadmin, but i coded some in php but i don't wanna drown u with code so ill give u only the status and tenderi table sql dump) CREATE DATABASE /*!32312 IF NOT EXISTS*/`dino` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `dino`; /*Table structure for table `status` */ DROP TABLE IF EXISTS `status`; CREATE TABLE `status` ( `Namjena` varchar(50) DEFAULT NULL, `Zapisano` datetime DEFAULT NULL, `Promjena` datetime DEFAULT NULL, `StatusID` enum('Ponuda napravljena','ne nuditi','isteko rok','ponuda prihvacena','sklopljen ugovor') NOT NULL DEFAULT 'Ponuda napravljena', PRIMARY KEY (`StatusID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC; /*Data for the table `status` */ insert into `status`(`Namjena`,`Zapisano`,`Promjena`,`StatusID`) values ('Ponuda napravljena za Tender','2009-06-14 23:11:21','2009-06-15 23:11:23','Ponuda napravljena'),('Na tender se nece nuditi','2009-06-14 23:11:41','2009-06-28 23:11:44','ne nuditi'); /*Table structure for table `tenderi` */ DROP TABLE IF EXISTS `tenderi`; CREATE TABLE `tenderi` ( `TenderID` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `RedniBroj` smallint(5) unsigned NOT NULL, `BrProtTend` varchar(50) NOT NULL, `ImeTendera` varchar(100) NOT NULL, `Predmet` varchar(255) DEFAULT NULL, `DatumLicitacije` date DEFAULT NULL, `PosebniKriteriji` varchar(255) DEFAULT NULL, `UsloviPlacanja` varchar(255) DEFAULT NULL, `ProcjenaVrijednosti` float DEFAULT NULL, `LicitacionaGarancija` float DEFAULT NULL, `OpcijaLG` varchar(2) DEFAULT NULL, `Valuta` enum('KM','$','EUR') DEFAULT NULL, `Paritet` float DEFAULT NULL, `Lokacija` varchar(255) DEFAULT NULL, `IzvedGaranc` float DEFAULT NULL, `AvansnaGarancija` float DEFAULT NULL, `KomitentID` smallint(6) DEFAULT NULL, `Drzavaid` enum('BIH','USA','HRV','SRB','SLO') DEFAULT NULL, `InvestitorID` smallint(6) DEFAULT NULL, `NosiocPoslaID` varchar(50) DEFAULT NULL, `OstaliUcesnici` varchar(255) DEFAULT NULL, `Telefon` varchar(50) DEFAULT NULL, `Kontakt` varchar(200) DEFAULT NULL, `Zapisano` datetime DEFAULT NULL, `Promjena` datetime DEFAULT NULL, `RatingTendera` enum('1','2','3','4','5') NOT NULL, `Odlozeno` varchar(50) DEFAULT NULL, `Status` enum('ponudjen','ne nuditi','isteko rok','ponuda prihvacena','sklopljen ugovor') DEFAULT NULL, PRIMARY KEY (`TenderID`), UNIQUE KEY `RedniBroj` (`RedniBroj`), KEY `NosiocPoslaID` (`NosiocPoslaID`), KEY `Valuta` (`Valuta`), KEY `FK_tenderi` (`KomitentID`), KEY `Status` (`Status`), CONSTRAINT `tenderi_ibfk_4` FOREIGN KEY (`Status`) REFERENCES `status` (`StatusID`), CONSTRAINT `FK_tenderi` FOREIGN KEY (`KomitentID`) REFERENCES `komitenti` (`KomitentID`), CONSTRAINT `tenderi_ibfk_1` FOREIGN KEY (`NosiocPoslaID`) REFERENCES `nosiocposla` (`NosiocPoslaID`), CONSTRAINT `tenderi_ibfk_3` FOREIGN KEY (`Valuta`) REFERENCES `kursnalista` (`ValutaID`) ) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC; /*Data for the table `tenderi` */ (the names are in Bosnian but u should get the gist)