[Counting Multiple Levels?]

I have a column called 'Progress' which updates as a row is processed. I have the following to count how many are in which stage:

SELECT Progress, COUNT(*) AS counter
FROM finished
GROUP BY Progress

Inside this I would also like to count where progress has the value of "1", but where there is a note added. So for example:

SELECT * FROM finished WHERE Progress=1 AND notes != 0

Is it possible to check both of these things in the same query and group them separately?

[CRON Job send email to users in DB?]

If i've got a database of users that have filled out a form, can I use a cron job to send an automated email? If so, what is the best way to "loop" it so that it sends the email once to each user?

 $data = mysql_query("
 SELECT *
 FROM completed
 WHERE
 followupsent='0000-00-00 00:00:00'
 AND valuesent + INTERVAL 4 DAY <= NOW()
 ")
 or die(mysql_error()); 
 
 while($info = mysql_fetch_array( $data )) 
{
}

This checks to see if "followupsent" has been updated already as it updates with NOW() when it sends and also checks to see how many days since the value was sent.

 

I'm worried that by putting the email sending information in the while tags is going to loop for each row and end up sending a ton of emails.

 

Would using

 if($info = mysql_fetch_array( $data )) 
{
}

Send out to the first in the database and then the CRON Job handle the rest by checking every minute which one is next?

 

Cheers

[Putting an array in to a CSV using fputcsv]

The CSV is being generated by the data from the database, not the other way.

[Putting an array in to a CSV using fputcsv]

It wasn't the relation that wasn't working, if I remember correctly it was to do with extracting the data to create a PDF the other side. As I say, I can't remember 100%, although reading Barand's last post, it makes me think it was possible, I just didn't know how!

[Putting an array in to a CSV using fputcsv]

For these fields I can't use a datetime field, I can't remember why, but it won't work... I set these up a few months ago and it was either give the month/day etc a different column or make it an array. I tried datetime and it was conflicting with what I was doing if I remember correctly.

[Putting an array in to a CSV using fputcsv]

Hi Barand, Hope you're well.

 

The relation is because there are 5 or 6 different tables for different things, but all have the UserID field so they can be joined.

[Putting an array in to a CSV using fputcsv]

Hi guys,

 

I've got quite a few fields in my tables that i've serialised to keep the number of fields down. For everything else that works perfect as it stores the data and when needed I can use the following as an example:

$dateofbirth = unserialize($row['dateofbirth']);
$dobday = $dateofbirth[0];
$dobmonth = $dateofbirth[1];
$dobyear = $dateofbirth[2];

 

 

Date of birth is stored as dd,mm,yyyy and for everything else I can call it fine. My issue is now that i'm trying to use fputcsv to create a csv file using the following:

$result = mysqli_query($con, 'SELECT u.user_id, b.dateofbirth FROM Users u INNER JOIN Basic b USING (user_id) ORDER BY user_id DESC');


$fp = fopen('latest.csv', 'w');
 
fputcsv($fp, array('User ID', 'DOB' ));

 

 

 

The CSV generates, but for the date of birth column in the csv it outputs as "a:3:{i:0;s:2:"03";i:1;s:2:"02";i:2;s:4:"1986";}" because it's obviously still serialised. What is my best and or easiest way of handling these fields?

 

Many thanks in advance.

[Making documents only accessible to specific users?]

I thought it was a bad idea to store large binary data in a database?

[Making documents only accessible to specific users?]

I have done away with encrypting the file name now, and am checking if they are the user from the database.

 

Which option would you suggest is best for encrypting the files? The website will be using HTTPS anyway, just not currently.

Cheers

[Making documents only accessible to specific users?]

Well, in this case instead of build a new layer of security based on the web server (most likely a shared hosting) for multiple users, I'd be consider to use the database as an upload storage.

It's currently being built on shared storage, but moving to dedicated hosting due to the nature of the files to be hosted.

[Making documents only accessible to specific users?]

Sorry, you're right, I completely read your structure wrong, it's currently like this:

 

 | 

 |-[my_website_root]

 |      |

 |      |-[css]

 |

 |      |-[images]

 |

 |-[documents]

 

So if I understand correctly, on the server I need to put the files in / instead of /www ?

 

I currently download the file using:

    if (!isset($_SESSION['UserID']))
    {
        echo 'None';
		exit;
    }
$file = '../documents/'.$hashedID.'.'.$FileType;

if (file_exists($file)) {
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename=' . basename($file));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file));
    ob_clean();
    flush();
    readfile($file);
    exit;
 } else { echo 'error'; }

I know I need to change the path for the documents in that, but basically if I add a database query (select where documentID = documentID where userID = Session[userID] for example) then that should be fairly secure?

[Making documents only accessible to specific users?]

Well I have a page called show-docs.php and that loads all of the docs from the database for that user, each document has a hashedID which is generated using a second (different from the password one) salt, called UpSalt. When uploading the file, the hashedID is generated and stored in the documents table along with the UserID. When a user is on show-docs.php and they click one of the items in the list, the page then grabs that user's UpSalt, and uses their session ID to identify the correct file in the documents folder.

 

The thinking behind this is that each document when uploaded will have it's own unique generated filename and that is simply matched up when trying to download it.

 

I'm actually thinking now that it would be a good idea to run a small query to check the document stored in the database belongs to the current user in the session, that wouldn't take up much resources would it?

 

So far in my tests that is working fine, but as Psycho says, the files aren't secure, just hidden... Could you guys point me in the direction of making the directory unaccessible?

 

Edit:

 

Psycho, you posted this:
 

 | 

 |-[my_website_root]

 |        |

 |        |-[images]

 |        |-[style_sheets]

 |        |-[pages]

 |

 |-[files_for_download]

Which i'm not too sure I understand currently... I have the following:

 

 | 

 |-[my_website_root]

 |

 |-[css]

 |-[documents]

 |-[images]

[Making documents only accessible to specific users?]

Would the way i've done it not be secure enough? It works for only being able to load your own files from early testing that i've done.

[Making documents only accessible to specific users?]

I've gone about it slightly differently, how secure does this sound? I'm using the document name, along with their UserID and Salt to generate a hashed file name for the file. I'm then running a script that is decoding that using the session username and document ID they clicked through from to download the file.

[Making documents only accessible to specific users?]

I'm storing website files online and each user can upload their own files and admin can upload files for that user specifically. How would I go about making sure nobody else can download their PDF file? Would it be a case of assigning a folder for each user's documents and not allowing access to any other user to that folder?

Thanks in advance.

[Group different results together if date (not time) is the same?]

Ignore this, i've sorted it. 

[Group different results together if date (not time) is the same?]

Apologies, my query is:

        SELECT *
	FROM Races
	WHERE SeasonNumber = '$SeasonNumber' AND LeagueID = '$LeagueID'
	ORDER BY Date ASC

[Group different results together if date (not time) is the same?]

Currently i'm using the following:

SELECT res.*, u.*, t.*
FROM Results res
INNER JOIN Users u USING (UserID)
INNER JOIN Teams t USING (TeamID)
WHERE RaceID = '$RaceID' AND Position = '1'

Which is getting race details.

<div class="ScheduleRace" style="background-image:url(images/tracks/<?=$row['Image']?>.jpg);">
    <div class="topstrip">
    <div style="float:left;"><?= 'Raceday '.$row['Raceday'].' - Race '.$row['RaceNum'].'. <strong>'; if ($row['Title'] !== '') { echo $row['Title'].' '; } echo $row['Track'].'</strong></div><div style="float:right;">'.date("d/m/Y - H:i", strtotime($row['Date'])).'</div><div style="clear:both;"></div>';?>
    </div>
    </div>

Each raceday consists of 3 races, the track, date, raceday is the same, but the times change. Do I need to have a second while inside this one to have the individual race details to group them together as follows:

<div class="ScheduleRace" style="background-image:url(images/tracks/<?=$row['Image'];?>.jpg);">
<?='Raceday '.$row['Raceday'].' - '.date("d/m/Y - H:i", strtotime($row['Date'])).'; if ($row['Title'] !== '') { echo $row['Title'].' '; } ?>

  //SECOND WHILE STATEMENT HERE

    <div class="topstrip">
    <div style="float:left;"><?= 'Race '.$row['RaceNum'];?></div>
    <div style="float:right;"><?= '.date("H:i", strtotime($row['Date']));?></div>
    </div>

    <div class="topstrip">
    <div style="float:left;"><?= 'Race '.$row['RaceNum'];?></div>
    <div style="float:right;"><?= '.date("H:i", strtotime($row['Date']));?></div>
    </div>

    <div class="topstrip">
    <div style="float:left;"><?= 'Race '.$row['RaceNum'];?></div>
    <div style="float:right;"><?= '.date("H:i", strtotime($row['Date']));?></div>
    </div>
  

</div>

Or is there a way to group the date and image and still loop the rest of the results inside the divs produced?

[foreach to insert multiple rows...]

I thought that too. I've got it working now how Jacques1 suggested, so thanks Jacques1.

 

However it's using multiple inserts rather than a single one.

[foreach to insert multiple rows...]

If i'm getting

$_POST['UserID1'] $_POST['TeamID1'] $_POST['Points1'] $_POST['UserID2'] $_POST['TeamID2'] $_POST['Points2']

I'm completely confused of how to go from that to 

INSERT INTO table_name (... cols ...) VALUES (... value set 1...), ( ... value set 2...)

Where each numerical value is posted to it's own row without specifically assigning each one of the values individually.

[foreach to insert multiple rows...]

It's something that was suggested to me last night for gathering all of the post data, normally I'd have assigned all of the data to variables manually.

[foreach to insert multiple rows...]

Yes, I do realise that... Nobody else has access to it.

[foreach to insert multiple rows...]

I'm getting the data from a form and using this to get the variables:

foreach($_POST as $key => $value){
  $$key = $value;
}

I knew of the security issues, but this is an admin area only accessible by me, but Is that bad practice?

[foreach to insert multiple rows...]

Hi guys. I currently have the following:

 

$UserID1

$TeamID1

$Points1

 

Where the value goes from 1 to 24, I need to insert all of these in to their own rows in a database how would I be best off doing that? Would I be able to use a foreach statement whereby the number increases? Or is there another way that doesn't involve doing 24 inserts?

 

Thanks in advance!

[Related values not showing in phpMyAdmin?]

This is accessing from cpanel in to phpmyadmin, I have access to all of my tables. I deleted the table and started again, as soon as I relate them, they vanish.