kool_samule

Hi Keith, Thanks for that, however, I get this error when I try either: Which refers to the table where the results are displayed....

Hi Chaps, I have some code, which I can't get to work, think I'm missing something somewhere. I want to access two tables, and filter them by column 'FK_projid', which is in a URL parameter 'id' $colname_rsJobs = "-1"; if (isset($_GET['id'])) { $colname_rsJobs = $_GET['id']; } mysql_select_db($database_conndb2, $conndb2); $query_rsJobs = sprintf(" ( SELECT tbl_jobs.FK_projid, tbl_jobs.jobid, tbl_jobs.jobname, tbl_jobs.FK_langid, tbl_languaget.langtname, tbl_jobs.jobshipped FROM tbl_projects INNER JOIN tbl_jobs ON tbl_projects.projid=tbl_jobs.FK_projid INNER JOIN tbl_languaget ON tbl_languaget.langtid=tbl_jobs.FK_langid ) UNION ( SELECT tbl_jobxml.FK_projid, tbl_jobxml.jobid, tbl_jobxml.jobname, tbl_jobxml.FK_langid, tbl_languaget.langtname, tbl_jobxml.jobshipped FROM tbl_projects INNER JOIN tbl_jobxml ON tbl_projects.projid=tbl_jobxml.FK_projid INNER JOIN tbl_languaget ON tbl_languaget.langtid=tbl_jobxml.FK_langid ) WHERE FK_projid = %s", GetSQLValueString($colname_rsJobs, "int")); $rsJobs = mysql_query($query_rsJobs, $conndb2) or die(mysql_error()); //$row_rsJobs = mysql_fetch_assoc($rsJobs); $totalRows_rsJobs = mysql_num_rows($rsJobs); I get the error: 3525 is correct, but I don't know what is stopping the code from working...any ideas?

Awesome! Cheers Keith, you've done it again. Manyt thanks, Samuel

Hi Keith, thanks but I got this error: "Every derived table must have its own alias"

Hi Chaps, Probably a really easy one for you: I have some PHP/SQL code, that looks at three tables and SUMs up data from a column. The problem is the data isn't SUMing up correctly, it places the three results after another, instead of SUMing up: <?php // Total Up Words (Net) $query = " ( SELECT tbl_jobs.jobwnet, SUM(jobwnet) FROM tbl_projects INNER JOIN tbl_jobs ON tbl_projects.projid=tbl_jobs.FK_projid INNER JOIN tbl_user_main ON tbl_user_main.userid=tbl_jobs.FK_usertranslationid WHERE tbl_jobs.jobtransih='y' AND tbl_jobs.jobtranscomplete='n' ) UNION ( SELECT tbl_jobtransline.jobwnet, SUM(jobwnet) FROM tbl_projects INNER JOIN tbl_jobtransline ON tbl_projects.projid=tbl_jobtransline.FK_projid INNER JOIN tbl_user_main ON tbl_user_main.userid=tbl_jobtransline.FK_usertranslationid WHERE tbl_jobtransline.jobtransih='y' AND tbl_jobtransline.jobtranscomplete='n' ) UNION ( SELECT tbl_jobxml.jobwnet, SUM(jobwnet) FROM tbl_projects INNER JOIN tbl_jobxml ON tbl_projects.projid=tbl_jobxml.FK_projid INNER JOIN tbl_user_main ON tbl_user_main.userid=tbl_jobxml.FK_usertranslationid WHERE tbl_jobxml.jobtransih='y' AND tbl_jobxml.jobtranscomplete='n' ) "; $result = mysql_query($query) or die(mysql_error()); // Print out result while($row = mysql_fetch_array($result)){ echo $row['SUM(jobwnet)']; } ?> E.g: tbl_jobs.jobwnet, SUM(jobwnet) = 100 tbl_jobtransline.jobwnet, SUM(jobwnet) = 200 tbl_jobxml.jobwnet, SUM(jobwnet) = 300 Required result = 600 Code result = 100200300

Sorted it with using the UNION syntax.

Hi, I want to merge the results from both queries into one table. The data is related, just stored in two tables. I need to end up with a table what looks something like this: projid title job type (table) 3001 doc_title normal (tbl_jobs) 3002 doc_title transline (tbl_jobtransline) 3003 doc_title normal (tbl_jobs) I hope this is clear and I haven't missed something obvious!?

Hi Keith, Thanks for that, I'll look into the LEFT OUTER JOIN method tomorrow. The queries I posted are completely seperate, i.e., they are on two seperate pages, one for tbl_jobs, the other for tbl_jobtransline. tbl_projects | -------- -------- | | tbl_jobs tbl_jobtransline | | results results What I want it to merge the results from the two pages into one query/php loop on one page. tbl_projects | -------- -------- | | tbl_jobs tbl_jobtransline | | ----------------- | results Hopefully that makes sense?

Hi Keith, Thanks for the info, are JOINs different to INNER JOINs? I'm a newbie at this and not sure where to start.

Basically, I have two similar MySQL tables: 1. tbl_jobs 2. tbl_jobtransline My current query will show results for tbl_jobs (that are incomplete). My PHP code loops through this query to display the results in the way I want. What I need is to alter the query to show results for both tbl_jobs and tbl_jobtransline.

Hi Chaps, I have a bit of PHP code, that loops through a MySQL table (jobs) and displays all records that have not been complete. The code and the MySQL query work fine. What I want to know is whether the Query can be altered to loop through 2 tables. Here is my current query: mysql_select_db($database_conndb2, $conndb2); $query_rsJobs = " SELECT tbl_projects.projid, tbl_projects.projtitle, tbl_jobs.jobid, tbl_jobs.FK_projid, tbl_jobs.jobname, tbl_jobs.FK_langid, tbl_languaget.langtname, tbl_jobs.jobpages, tbl_jobs.jobshipped FROM tbl_projects INNER JOIN tbl_jobs ON tbl_projects.projid=tbl_jobs.FK_projid INNER JOIN tbl_languaget ON tbl_languaget.langtid=tbl_jobs.FK_langid WHERE tbl_jobs.jobshipped='n' ORDER BY FK_projid ASC"; $rsJobs = mysql_query($query_rsJobs, $conndb2) or die(mysql_error()); //$row_rsJobs = mysql_fetch_assoc($rsJobs); $totalRows_rsJobs = mysql_num_rows($rsJobs); Here is my table that displays the results: <table> <tr> <td>Document Title</td> <td>Language</td> <td>Pages</td> <td>Edit</td> <td>Remove</td> </tr> <?php $previousProject = ''; if ($totalRows_rsJobs > 0) { // Show if recordset not empty while ($row_rsJobs = mysql_fetch_assoc($rsJobs)) { if ($previousProject != $row_rsJobs['projid']) { // for every Project, show the Project ID ?> <tr> <td><?php echo $row_rsJobs['projid'] ?></td> </tr> <?php $previousProject = $row_rsJobs['projid']; } ?> <tr> <td><a href="jobsheet_details.php?id=<?php echo $row_rsJobs['jobid']; ?>&proj=<?php echo $row_rsJobs['projid']; ?>"><?php echo $row_rsJobs['jobname']; ?></a></td> <td><?php echo $row_rsJobs['langtname']; ?></td> <td><?php echo $row_rsJobs['jobpages']; ?></td> <td><a href="jobsheet_edit.php?id=<?php echo $row_rsJobs['FK_projid']; ?>&job=<?php echo $row_rsJobs['jobid']; ?>">Edit</a></td> <td><a href="jobsheet_remove.php?id=<?php echo $row_rsJobs['FK_projid']; ?>&job=<?php echo $row_rsJobs['jobid']; ?>">Remove</a></td> </tr> <?php } while ($row_rsJobs = mysql_fetch_assoc($rsJobs)); ?> <?php } // Show if recordset not empty ?> </table> And finally, this is the Query that I need adding to my current query to show the resutls from both tables: mysql_select_db($database_conndb2, $conndb2); $query_rsJobTrans = " SELECT tbl_projects.projid, tbl_projects.projtitle, tbl_jobtransline.jobid, tbl_jobtransline.FK_projid, tbl_jobtransline, tbl_jobtransline.FK_langid, tbl_languaget.langtname, tbl_jobtransline.jobpages, tbl_jobtransline.jobshipped FROM tbl_projects INNER JOIN tbl_jobtransline ON tbl_projects.projid=tbl_jobtransline.FK_projid INNER JOIN tbl_languaget ON tbl_languaget.langtid=tbl_jobtransline.FK_langid WHERE tbl_jobtransline.jobshipped='n' ORDER BY FK_projid ASC"; $rsJobTrans = mysql_query($query_rsJobTrans, $conndb2) or die(mysql_error()); //$row_rsJobTrans = mysql_fetch_assoc($rsJobTrans); $totalRows_rsJobTrans = mysql_num_rows($rsJobTrans); ?> I'm not sure if this is possible and have tried myself but seem to get in a bit of a muddle, as both tables use a Foreign Key from the Projects and LanguageT tables, any help would be most appreciated!

Nice one, sorted it! Cheers

Hi Bricktop, Yes, MySQL is installed on the server, so too PHP which is configured correctly. Tested phpinfo.php and looks fine. Does my script look OK or have I missed something?

Hi Bricktop, The mkdir works fine on the server (it creates it). The problem is when the directory already exists, and you try to create it again, you should see an error message, rather than an HTTP 500 message. Does that make sense?

Hi Bricktop, MySQL is installed and working, I can connect to it fine, do all the usual stuff, add, edit and delete records. On my development server (Apache) the $command works fine and runs, after migrating to the IIS server (and editing the connection settings), I can't seem to get this to work.

I have a simple mkdir script, that creates a directory that contains 3 sub-fodlers, on my server. The script works fine on my Apache server and if the directory exists, the error message is displayed. However on my IIS, I get an HTTP error, I don't think that the script is getting to the "if" or "else" functions : PHP Warning: mkdir() [function.mkdir]: File exists in C:\Web\QT\admin\script.php on line 85 // URL $url = "//Server/Folder1/Folder2/"; // Create It $create = mkdir("$url"); mkdir("$url/Sub1"); mkdir("$url/Sub2"); mkdir("$url/Sub3"); if ($create) { $mess = $ref = $_SERVER['HTTP_REFERER']; header( 'refresh: 0; url='.$ref);; $query = sprintf("UPDATE tbl_freelancer SET freedir='y' WHERE freeid=%s", GetSQLValueString($colname_rsFreelance_Dir, "int")); $result = mysql_query($query, $conndb2) or die(mysql_error()); } else { $mess = "<p>Directory could not be created.</p>"; } Is there a way of getting this to work?

Hi guys, I have a simple script that backup the contents of a database and place the file on the server. The problem is that this script works fine on my Apache server, but not on the IIS server (HTTP 500 error). Script: <?php $backupFile = "../DBJobs_" . date("Y-m-d"); $command = "mysqldump --opt -h localhost -u (user) -p(password) dbjobs > $backupFile.sql"; system($command); if ($command) { $mess = "<p>Backup file created!</p>"; } else { $mess = "<p>There was a problem with the backup routine.</p>"; } ?> Do I have to change the $command variable or the system function? Any help would be very appreciated.

i'll try updating mysql and see how that goes. many thanks

hi guys, i have a php / mysql web application that i've developed on an apache server. i've migrated it to an iis server, but now some parts of the app cannot run. i'm getting errors: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause does anyone have an idea on what might be causing this error and how to solve it?