kool_samule

October 9, 2009

Hi Keith,

Thanks for that, however, I get this error when I try either:

PHP Notice: Undefined variable: row_rsJobs in C:\Inetpub\QuickTrace\admin\project_details.php on line 330 [repeated]

Which refers to the table where the results are displayed....

October 9, 2009

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:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE FK_projid = 3525' at line 32

3525 is correct, but I don't know what is stopping the code from working...any ideas?

October 9, 2009

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

October 9, 2009

Hi Keith, thanks but I got this error:

"Every derived table must have its own alias"

October 9, 2009

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

October 7, 2009

Sorted it with using the UNION syntax.

October 7, 2009

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!?

October 6, 2009

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?

October 6, 2009

Hi Keith,

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

October 6, 2009

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.

October 6, 2009

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!

September 25, 2009

Nice one, sorted it! Cheers

September 25, 2009

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?

September 25, 2009

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?

September 25, 2009

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.

September 25, 2009

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?

September 25, 2009

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.

September 21, 2009

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

September 21, 2009

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?

Sign In

kool_samule

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by kool_samule

[SOLVED] Filter 2 UNION joined table columns by URL Parameter

[SOLVED] Filter 2 UNION joined table columns by URL Parameter

[SOLVED] Simple SUM code

[SOLVED] Simple SUM code

[SOLVED] Simple SUM code

PHP loop through 2 MySQL tables?

PHP loop through 2 MySQL tables?

PHP loop through 2 MySQL tables?

PHP loop through 2 MySQL tables?

PHP loop through 2 MySQL tables?

PHP loop through 2 MySQL tables?

[SOLVED] MkDir PHP problem

Backup MySQL PHP script

[SOLVED] MkDir PHP problem

Backup MySQL PHP script

[SOLVED] MkDir PHP problem

Backup MySQL PHP script

Web App OK on Apache, NOT OK on IIS

Web App OK on Apache, NOT OK on IIS

Browse

Activity

Important Information