Skipjackrick

March 2, 2011

also just get their IP and store it so they can only vote once

Thank you....That's defnitely an easy fix you've got there.

However, Wouldn't storing the IP fill up my database with a ton of information? Is there a better way to handle that?

March 2, 2011

I am trying to find some example code where the user could click a button to approve or disapprove a certain topic. Or even like a voting type of code where they vote yes or no.

The database would just update a running count each time the user clicked on the approve or disapprove buttons.

I'll also need each user to only be allowed to vote once on a particular topic.

Any ideas for some examples somewhere?

Seems like I could write something like

<?php

//topic variable passed through a link I assume
$topic = 2343;

//user clicks on approve
$approve = "UPDATE topic SET a_count = a_count + 1 WHERE topic_id = $topic";

//user clicks on disapprove
$disapprove = "UPDATE topic SET d_count = d_count + 1 WHERE topic_id = $topic";

//The rest I am unsure of how to control the user from clicking approve 500 times.  Maybe in a $_SESSION?

?>

February 7, 2011

OH!!!

I get it...My WHERE statement should also be in the right table. I hope that works.

Right table = the table on the right hand side of the join.

Left table = Submit

Right table = select angler,

max(length) as mlength

from submit

group by angler

~judda

February 6, 2011

I'm guessing that the right table doesn't return the 8 extra rows you were expecting or something along those lines.

~judda

Right table?

I am only using one table named Submit.

February 5, 2011

angler is being returned by both the submit table and the subquery. In order to fix that you just need to qualify the angler which is in the outermost select statement (i.e. dt.angler)

~judda

Thanks..

So I've rearranged it a little bit...It seems to work but its only returning 7 rows. When there should be like 15 rows because there are more species than that. Do you happen to know why it might be doing that?

<?php
      //Query for records region 1
      $records1 = "SELECT 
                       angler
                       ,team_id
                       ,dt.species_id
                       ,length
                       ,image
                       FROM submit
                         join (SELECT 
                                  species_id
                                  ,max(length) as mlength
                               FROM submit
                                GROUP BY species_id
                               ) dt
                           ON (submit.species_id,submit.length) = (dt.species_id,dt.mlength)
                        WHERE submit.region_id = 1
                        ORDER BY submit.species_id
                ";
      $records_result1 = mysql_query($records1) or die(mysql_error());
?>

February 4, 2011

I am trying to get the largest fish that each angler caught but also associate the correct data in the row.

With the following code I get an error message

column 'angler' is ambiguous

Do you know how to fix that? I am still learning this join and on stuff.

select angler, 
       team_id, 
       species_id, 
       length, 
       image
  from submit
  join (select angler,
               max(length) as mlength
          from submit
         group by angler) dt
    on (submit.angler,submit.length) = (dt.angler,dt.mlength)
where submit.region_id = 1
order by submit.species_id

January 17, 2011

Nevermind....

Boy that was easy....

January 17, 2011

I know we were just discussing this on this other topic...

http://www.phpfreaks.com/forums/mysql-help/find-the-largest-value-for-each-group-and-sum/

And it worked beautifully....

But now I want to group those results by the team that each angler is on.... Or SUM those results and group them by team... Is that possible?

For example, the following code outputs.......

<?php
//connect to the db
include 'db_connect.php';

      //Testing for total inches
      $total_inches = "SELECT
                angler, team_id, SUM(mlength)
                FROM (
                         SELECT angler, yyyy, team_id, MAX(length) AS mlength
                            FROM submit WHERE yyyy=2010 GROUP BY angler, species_id
                     ) AS zz
      GROUP BY angler
      ORDER BY SUM(mlength) DESC
                ";
      $total_inches_result = mysql_query($total_inches) or die(mysql_error());

      $RowCount = mysql_num_rows($total_inches_result);
?>

January 17, 2011

AHA!!

I need to use SUM(mlength)

Has this issue been resolved?

Yeah, I mostly got it figured out.....

I like your avatar....you must be a male? LOL....

I work as a pharmaceutical scientist during the day.

January 17, 2011

I didn't read all your code... your query is wrong.... here is a small example (one way to solve your original questions) that you can use/adjust according to your needs.
SELECT angler, SUM(mlenght)
FROM (
        SELECT angler, MAX(lenght) AS mlenght
           FROM anglers GROUP BY angler, specie
     ) AS zz
group by angler;

THANKS SO MUCH!!!!

This is exactly what I needed.....now I am just working through customizing it for me....

January 17, 2011

AHA!!

I need to use SUM(mlength)

January 17, 2011

I didn't read all your code... your query is wrong.... here is a small example (one way to solve your original questions) that you can use/adjust according to your needs.
SELECT angler, SUM(mlenght)
FROM (
        SELECT angler, MAX(lenght) AS mlenght
           FROM anglers GROUP BY angler, specie
     ) AS zz
group by angler;

This actually got results...but how do I display them using php?

It returned 342 rows..

<?php
//connect to the db
include 'db_connect.php';


      //Testing for total inches
      $total_inches = "SELECT
                angler, SUM(mlength) 
                FROM (
                         SELECT angler, MAX(length) AS mlength
                            FROM submit GROUP BY angler, species_id
                     ) AS zz
      GROUP BY angler
                ";
      $total_inches_result = mysql_query($total_inches) or die(mysql_error());

      $RowCount = mysql_num_rows($total_inches_result);


$tablehead = "<table border='1'><tr> <th>Rank</th> <th>Team</th> <th>Angler</th> <th>Species</th> <th>Length</th></tr>";
$tablefoot = "</table>";

echo "There are $RowCount rows in your results.<br />";

$rank=1;
while($row = mysql_fetch_array($total_inches_result)) {

$team = $row['team_id'];
$angler = $row['angler'];
$species = $row['species'];
$total = $row['zz'];

   $tabledetails .=<<<EOD
           <tr>
              <td>
                 $rank
              </td>
              <td>
                 $team
              </td>
              <td>
                 $angler
              </td>
              <td>
                 $species
              </td>
              <td>
                 $total
              </td>
           <tr>
EOD;
$rank++;
} 

$table .=<<<EOD
       $tablehead
       $tabledetails
       $tablefoot
EOD;

print $table;

?>

January 17, 2011

Ok....

I am just sort of working through the tutorial you sent me to.. I am getting results but its not displaying my results. Do you see anything weird?

<?php
//connect to the db
include 'db_connect.php';


      //Testing for total inches
      $total_inches = "SELECT
                team_id, angler, species_id, length 
                FROM submit
                WHERE length = (SELECT MAX(length) from submit as total where total.angler = submit.species_id)
                ";
      $total_inches_result = mysql_query($total_inches) or die(mysql_error());

      $RowCount = mysql_num_rows($total_inches_result);


$tablehead = "<table border='1'><tr> <th>Rank</th> <th>Team</th> <th>Angler</th> <th>Species</th> <th>Length</th></tr>";
$tablefoot = "</table>";

echo "There are $RowCount rows in your results.<br />";

$rank=1;
while($row = mysql_fetch_array($total_inches_result)) {

$team = $row['team_id'];
$angler = $row['angler'];
$species = $row['species'];
$length = $row['length'];

   $tabledetails .=<<<EOD
           <tr>
              <td>
                 $rank
              </td>
              <td>
                 $team
              </td>
              <td>
                 $angler
              </td>
              <td>
                 $species
              </td>
              <td>
                 $length
              </td>
           <tr>
EOD;
$rank++;
} 

$table .=<<<EOD
       $tablehead
       $anglerdetails
       $tablefoot
EOD;

print $table;

?>

January 17, 2011

In case you were interested....here is my table structure

Fields

+-------+------------+------------+----------------+-----------+-----------+

id team_id angler species_id points length

+-------+------------+------------+----------------+-----------+-----------+

January 17, 2011

@Skipjackrich :

here is something for you to explore:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Thanks let me read that....just a brief look over it looks like what I need.

January 17, 2011

using this:
SELECT SUM(size), name FROM s1 GROUP BY name ORDER BY size DESC LIMIT 1
you can get the first part of it (and angler A is 36 not 37). using this as a foundation, and applying it to the other fields in your table, you sould get the second part ok on your own. Any further problems post up your full table structure

Got an empty result..

<?php
      //Testing for total inches
      $total_inches = "SELECT
                SUM(length), angler
                FROM submit
                GROUP BY length
                ORDER BY length DESC LIMIT 1
                ";
      $total_inches_result = mysql_query($total_inches) or die(mysql_error());

      $RowCount = mysql_num_rows($total_inches_result);


$tablehead = "<table border='1'><tr> <th>Rank</th> <th>Team</th> <th>Total Inches</th></tr>";
$tablefoot = "</table>";

$rank=1;
while($row = mysql_fetch_assoc($total_inches_result)) {

$team = $row['team_id'];
$total = $row['SUM(length)'];
   $tabledetails .=<<<EOD
           <tr>
              <td>
                 $rank
              </td>
              <td>
                 $team
              </td>
              <td>
                 $total
              </td>
              <td>
                 $min_length
              </td>
           <tr>
EOD;
$rank++;
} 

$table .=<<<EOD
       $tablehead
       $anglerdetails
       $tablefoot
EOD;

print $table;

?>

January 17, 2011

using this:
SELECT SUM(size), name FROM s1 GROUP BY name ORDER BY size DESC LIMIT 1
you can get the first part of it (and angler A is 36 not 37). using this as a foundation, and applying it to the other fields in your table, you sould get the second part ok on your own. Any further problems post up your full table structure

Thanks,

I'll give this a go and report back with the results.

January 17, 2011

I can't seem to wrap my brain around how to do this. I've tried using MAX() and tried writing it out long hand....I just can't seem to get this.

Here is an example of what I am trying to do. This is a fishing website so .... you get the drift.

Example, Team XYZ has two anglers

Angler A catches the following:

Bass = 16 inches

Bass = 18 inches

Catfish = 19 inches

Catfish = 17 inches

Angler B catches the following:

Bass = 17.5 inches

Bass = 18.3 inches

Catfish = 20 inches

Catfish = 21 inches

I want to return the Sum of the inches of only the LARGEST catch for each species for both anglers..

The result should be...

Angler A = 37 inches

Angler B = 39.3 inches

TEAM XYZ = 76.3 inches

Any thoughts?

January 7, 2011

It works like a champ!

Thanks!

I didn't think I could use it in a while loop.... Guess I learn things everyday!

<?php

include 'db_connect.php';

$userId=2;

    // Specify the directory that will store the users images with the desired folder structure
    $structure = './images/' .$userId. '';
    
    //First check to see if the directory already exists
    if (file_exists($structure)) { 
        echo "The directory exists"; 

    } else { 
          // create the directory and set permissions
          if (!mkdir($structure, 0777, true)) {
              die('Failed to create folder');
              chmod($structure, 0777);
              echo "The Directory was created";
          }
    }  

//Query the database for the images
$query_image = "SELECT *
	FROM submit
                WHERE user=$userId
	ORDER BY submit_id ASC LIMIT 5";

$image_result = mysql_query($query_image) or die(mysql_error());

// get count of how many rows in case we need that info
$rowCount = mysql_num_rows($image_result);


while($row = mysql_fetch_assoc($image_result)) {

//move files
$file = "./submitted_pics/{$row['image']}";
$newfile = "./images/$userId/{$row['image']}";

if( !file_exists($newfile) ) {  // in case script is executed multiple times.

if (!copy($file, $newfile)) {
echo "failed to copy $file...\n";
}
} else {
                 echo "File $file already exists in target directory . . .";
}
}

echo "Your copy was a success!  $rowCount pictures were copied.";

?>

January 6, 2011

Any update? Particularly on whether or not you've given it a run and seen what it generates?

Only issue I see is, you're using mysql_fetch_assoc and then turning it into an array manually? Why not use mysql_fetch_array?

Meh, I've been spoiled on framework queries, so I may be way off here.

Yeah it works but it only copies one file over. It returns the first result from the database and copies that file. I need to copy like 13,000 photos....not just one..

For me to do that manually it takes about 30 seconds per photo. 30 sec * 13,000 pics = 108 hours of copying photos.....LOL!

January 4, 2011

Ok fellas....this one has me stumped.

I am trying to copy a whole bunch of pictures from one directory into a new directory. However, the new directory will store the images based upon the user's id. Initially I stored all the images in one directory. HUGE mistake.. That single directory is over 5GB and takes and eternity to open and edit the files for 1 single user. So I need to separate them out.

Anyways, here is what I am trying to do in theory.

All the information about the user and the file they uploaded is stored in my database. But how do I create a script that is somewhat dynamic? One that will refresh the copy file script for each result.

Here is the general idea....or a conversation starter code i am working with so far.

<?php

include 'db_connect.php';


$userId=2;

          // Make the directory that will store the users images with the desired folder structure
          $structure = './images/' .$userId. '';

          // create the directory and set permissions
          if (!mkdir($structure, 0777, true)) {
              die('Failed to create folder');
              chmod($structure, 0777);
          }

//Query the database for the images
$query_image = "SELECT *
	FROM images
                WHERE angler=$userId
	ORDER BY submit_id DESC";

$image_result = mysql_query($query_image) or die(mysql_error());

// get count of how many rows in case we need that info
$rowCount = mysql_num_rows($image_result);

//get the results into an array by setting each result as a variable
while($row = mysql_fetch_assoc($image_result))
   $galleryresults[]= $row;


          //now move files
          $file = './submitted_pics/{$galleryresults[0]['image']}';
          $newfile = './images/' .$userId. '/{$galleryresults[0]['image']}';

          if (!copy($file, $newfile)) {
              echo "failed to copy $file...\n";
          }

?>

March 17, 2010

Thanks, I'll look for that php.ini-recommended file.

March 16, 2010

I am a bit worried about the statement at the beginning of my php.ini file.

It says.

; This is the default settings file for new PHP installations.

; By default, PHP installs itself with a configuration suitable for

; development purposes, and *NOT* for production purposes.

I was wondering if you guys had any recommendations for settings in a php.ini file for production purposes. I am clueless about this?

March 8, 2010

Ah yeah!!

Thanks!

I like that foreach method..... seems a bit easier to work with many different combinations of conditions.

March 8, 2010

Is it possible to do something like I have below?

Basically I've got several conditions that need to be true for an if statement.

     
<?php
$condition1=1;
$condition2=1;
$condition3=1;
$condition4=0;

if ( $condition1 == 1 && $condition2 == 1  && $condition3 == 1 && $condition4 == 1) 
      {
      //Update kayak man points
      $count_kmpoints = "UPDATE anglers 
            SET kayakman_points = kayakman_points + 1
            WHERE anglerId=$angler";
      $counted_kmpoints = mysql_query($count_kmpoints) or exit(mysql_error());

      } else {
      echo "Sorry, you haven't met the criteria to score any Kayak Man points";
      }
?>

Sign In

Skipjackrick

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by Skipjackrick

Approve / Disapprove code

Approve / Disapprove code

Help with Join

Help with Join

Help with Join

Help with Join

Find the largest value for each group and sum and then sum those results!?!?!

Find the largest value for each group and sum and then sum those results!?!?!

Find the largest value for each group and sum

Find the largest value for each group and sum

Find the largest value for each group and sum

Find the largest value for each group and sum

Find the largest value for each group and sum

Find the largest value for each group and sum

Find the largest value for each group and sum

Find the largest value for each group and sum

Find the largest value for each group and sum

Find the largest value for each group and sum

HELP: Copying Files using results from a DB

HELP: Copying Files using results from a DB

HELP: Copying Files using results from a DB

PHP.INI File Warning Statement

PHP.INI File Warning Statement

If Statement Syntax for Multiple conditions

If Statement Syntax for Multiple conditions

Browse

Activity

Important Information