whitedragon101
-
Posts
21 -
Joined
-
Last visited
Posts posted by whitedragon101
-
-
What would you recommend for securing access to phpMyAdmin? I am the developer and need remote access to phpMyAdmin which is on a server in another country (a linux machine with a LAMP stack at the owners office). At the moment for development it's just an internet accessible url and a username and password.
a) restrict access to phpMyAdmin to localhost and ssh into the server to get access
b) Allow access via an internet url and use 2 factor authentication (probably Google Authenticator)
c) keep it as username and password with public url and use a crazy long password to protect against brute force
-
12 minutes ago, guymclarenza said:
If you coded a shop, a forum should be a doddle.
Don't want to code a forum from scratch for no reason.
Just wondered if anyone had any particular recommendations for what forum they prefer to use.
-
Hi,
I have finished a webshop (coded in php, Javascript, MySQL) for a client and they want to add a Forum . Does anyone have any recommendations for a free forum I can just drop in with minimal setup ?
In an ideal world it would be good if it used MySQL so I can create a forum account at the same time they create their customer account, but its not the end of the world if that's not possible.
Many thanks
-
#1064 - 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 'table3 LEFT JOIN (SELECT lga_id , COUNT(schools.school_id_p) as num_of_scho' at line 15
-
MYSQL Version : 5.0.91
I have two queries which run fine however when I alias them and left join them I get a syntax error :
QUERY_1
SELECT * FROM( (SELECT lga_id , COUNT(school_id_p) as num_of_schools_unapproved FROM schools WHERE EXISTS ( SELECT * FROM orders WHERE approved = 'no' AND schools.school_id_p = orders.school_id) GROUP BY lga_id) AS table1 LEFT JOIN (SELECT lga_id_p, name FROM lga) AS table2 ON table1.lga_id = table2.lga_id_p )
produces:
lga_id num_of_schools_unapproved lga_id_p name
2 2 2 lga_name2
4 1 4 lga_name4
5 1 5 lga_name
which is correct
QUERY_2
SELECT lga_id , COUNT(schools.school_id_p) as num_of_schools FROM schools GROUP BY lga_id
produces :
lga_id num_of_schools
1 5
2 5
3 5
4 5
5 5
6 3
7 2
which is correct.
However when I try to call them table3 and table 4 and do a LEFT JOIN on the lga_id I get a syntax error. Any help much appreciated :
SELECT * FROM( SELECT * FROM( (SELECT lga_id , COUNT(school_id_p) as num_of_schools_unapproved FROM schools WHERE EXISTS ( SELECT * FROM orders WHERE approved = 'no' AND schools.school_id_p = orders.school_id) GROUP BY lga_id) AS table1 LEFT JOIN (SELECT lga_id_p, name FROM lga) AS table2 ON table1.lga_id = table2.lga_id_p ) AS table3 LEFT JOIN (SELECT lga_id , COUNT(schools.school_id_p) as num_of_schools FROM schools GROUP BY lga_id) AS table4 ON table3.lga_id = table4.lga_id )
-
Code 2 would work except you passing the variable $header to the mail functions instead of $headers.
Bingo
it works. Its always the stupid stuff that gets you Is this how these styled corporate emails are generally done. Just send out html in the email itself? Or do they use an iFrame or other such container linked to an html page on their servers?
You'll also want a while {} no a do {} while, because your first loop won't have $row_email['email'] defined otherwise.
I have already got the first row out when I wrote the MySQL query. I only do it that way so when I do my loops down the page I can just copy and paste the row name
$query_email = "SELECT email FROM customer WHERE email_yes_or_no != 'no' "; $email_result = mysql_query($query_email, $jobconnect) or die(mysql_error()); $row_email = mysql_fetch_assoc($email_result); $totalRows_email_result = mysql_num_rows($email_result);
Thanks thorpe
-
I am looping through the emails so there is a do loop at the bottom of each getting a new address for each MySQL row.
All three codes send the email, and all three only display the html markup itself but not styled text.
Code 1
//define the receiver of the email //define the subject of the email $subject = 'Test HTML email'; //create a boundary string. It must be unique //so we use the MD5 algorithm to generate a random hash $random_hash = md5(date('r', time())); //define the headers we want passed. Note that they are separated with \r\n $headers = "From: $from\r\nReply-To: $from"; //add boundary string and mime type specification $headers .= "\r\nContent-Type: multipart/alternative; boundary=\"PHP-alt-".$random_hash."\""; //define the body of the message. ob_start(); //Turn on output buffering ?> --PHP-alt-<?php echo $random_hash; ?> Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hello World!!! This is simple text email message. --PHP-alt-<?php echo $random_hash; ?> Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: 7bit <h2>Hello World!</h2> <p>This is something with <b>HTML</b> formatting.</p> --PHP-alt-<?php echo $random_hash; ?>-- <? //copy current buffer contents into $message variable and delete current output buffer $message = ob_get_clean(); do{ $email= $row_email['email']; $to=$email; $sentmail = @mail($to,$subject,$message,$headers); } while($row_email = mysql_fetch_assoc($email_result));Code 2
$from = $row_company_details['email']; $name=$_POST["name"]; $subject="hello"; //$message = file_get_contents('http://www.holtrecruitment.com/TEST_RECRUITMENT/webpages/management_area/newsletter.php'); $message = "<p>Hello</p>"; $headers="From:$name <$from>\r\n"; $headers .= "Reply-To: $from\r\n"; $headers .= "Date: " . date("r") . "\r\n"; $headers .= "Return-Path: $from\r\n"; $headers .= "MIME-Version: 1.0\r\n"; $headers .= "Message-ID: " . date("r") . $_SERVER["name"]."\r\n"; $headers .= "Content-Type: text/html; charset=utf-8\r\n"; $headers .= "X-Priority: 1\r\n"; $headers .= "Importance: High\r\n"; $headers .= "X-MXMail-Priority: High\r\n"; $headers .= "X-Mailer: PHP Mailer 1.0\r\n"; //////////////////// //message do{ $email= $row_email['email']; $to=$email; $sentmail = mail($to,$subject,$message,$header); } while($row_email = mysql_fetch_assoc($email_result));Code 3
$from = $row_company_details['email']; // $HTML = file_get_contents('http://www.holtrecruitment.com/TEST_RECRUITMENT/webpages/management_area/newsletter.php'); $HTML = "<p>THis is a test</p>"; $headers = "From: $from\r\n"; // Now we specify our MIME version $headers .= "MIME-Version: 1.0\r\n"; // Create a boundary so we know where to look for // the start of the data $boundary = uniqid("HTMLEMAIL"); // First we be nice and send a non-html version of our email $headers .= "Content-Type: multipart/alternative;". "boundary = $boundary\r\n\r\n"; $headers .= "This is a MIME encoded message.\r\n\r\n"; $headers .= "--$boundary\r\n". "Content-Type: text/plain; charset=ISO-8859-1\r\n". "Content-Transfer-Encoding: base64\r\n\r\n"; $headers .= chunk_split(base64_encode(strip_tags($HTML))); // Now we attach the HTML version $headers .= "--$boundary\r\n". "Content-Type: text/html; charset=ISO-8859-1\r\n". "Content-Transfer-Encoding: base64\r\n\r\n"; $headers .= chunk_split(base64_encode($HTML)); do{ $email= $row_email['email']; $to=$email; $sentmail = mail($to,$subject,"",$header); } while($row_email = mysql_fetch_assoc($email_result)); -
I have tried the top 4 or 5 scripts from googling "send html email using php." Every single one results in the html markup itself being displayed as text, not as a styled page.
I have many emails from companies where no matter what client I view it on I see a fully styled webpage with links and pics. How can this be achieved with php?
-
I still don't see why you just can't join it directly and combine the ON conditions.
Could you show me a code block of what you mean please.
Many thanks
John
-
Why do you have a subquery in the right join?
If I do a right join and the record from table2 is already in table1 it will join with it anyway giving duplicate fields for all the columns. ie there will be two columns called row_id in the result set.
So I was trying to use a subquery that would only give rows from table2 that are not in table1. That way when I join them there will be no overlap.
-
I have an aliased table called table1 and a real table in the database called table2.
table1 has been produced from many joins, subqueries and unions.
I want to produce a result-set that adds all the records from table1 to the records of table2 where the given row_id is not already present in table2.
eg
table2 (real table in database)
row_id
1
2
3
4
5
table1 (aliases table created with queries)
row_id | data_field1 | data_field2
1______data1______data2
2______data3______data4
desired result set
row_id | data_field1 | data_field2
1______data1______data2
2______data3______data4
3
4
5
I have tried the following where I attempt to create a table3 which only selects those records from table1 which are not already in table2 and then join the two aliased tables. However I get the error:
Unknown column 'table1.row_id' in 'where clause'
MySQL version - 5.1.47-community-log
SELECT * FROM( ....lots of subqueries, unions etc..... ) AS table1 RIGHT JOIN (SELECT table2.* FROM table2 WHERE table1.row_id != table2.row_id)AS table3 ON table1.row_id = table3.row_id
I have also tried to just create a view of table1 however I get the error:
#1349 - View's SELECT contains a subquery in the FROM clause
It seems from a quick google mysql can not create a view from complex queries
-
Thanks guys
Have used the strtotime() to convert the date to a unix timestamp.
Then used the php min() function to find the lowest date.
The used the gmdate("Y-m-d ", $lowestDate) function to convert the lowest date into a printable date again.
-
What I have is a table structured thus:
Job ID | Job Name | StartDate1 | EndDate1 | StartDate2 | EndDate2 | StartDate3 | EndDate3
I have been trying to get the earliest of the start dates (and after that I will need the latest of the end dates).
I want to display a table the just has the first two fields then the earliest and latest dates for each row like this:
Job ID | Job Name | Earliest Date | Latest Date
This is why I am having trouble finding a solution because it is for the ealiest and latest dates for each ROW not COLUMN.
I have been trying to do it like this.
($row_job_set is a row of a simple SELECT * query from the table described above ).
<table border="1"> <tr> <td>Job Id</td> <td>Job Name</td> <td>Earliest Date</td> <td>Latest Date</td> </tr> <?php do { $startDate1 = $row_job_set['startDate1']; $startDate2 = $row_job_set['startDate2']; $startDate3 = $row_job_set['startDate3']; $lowestDateQuery = "SELECT MIN('$startDate1','$startDate2','$startDate3')"; $lowestDate = mysql_query($lowestDateQuery); $dRow = mysql_fetch_row($lowestDate); $dResult = mysql_result($dRow,0); $totalRows_lowestDate= mysql_num_rows($lowestDate); ?> <tr> <td><?php echo $row_job_set['job_id']; ?></td> <td><?php echo $row_job_set['job_name']; ?></td> <td><?php echo $totalRows_lowestDate ?> </td> <td>Not yet implimented</td> </tr> <?php } while ($row_job_set = mysql_fetch_assoc($job_set)); ?> </table> -
This is the string I am passing into the MySQL console so need a semicolon to terminate the string. The following is an exact copy of a line I used in the console with DATEDIFF, which does return the difference between the two dates.
mysql > SELECT DATEDIFF('2012-10-09','2011-10-01');
works
However the query I need now is to get the lowest of the two dates and am again testing it in the console and:
mysql > SELECT MIN('2012-10-09','2011-10-01');
returns the error listed above.
-
The error is
ERROR 1064 (42000): 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 ' '2011-10-01')' at line 1
-
Need to get the lowest of 2 dates.
I am trying this syntax but it does not work. I am sure there is some simple change I need to make it work.
Any ideas?
SELECT MIN('2012-10-09','2011-10-01');
thanks
-
Fixed it
. As always was nothing complex just a stupid syntax error staring me in the face.PFMaBiSmAd's code at the command line was:
SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
so my php was
$difference = mysql_query("SELECT DATEDIFF($date2,$date1)");
and it turns out all i am missing was '
so this works just fine. and all the different ways I was trying to get at the variable all work
$difference = mysql_query("SELECT DATEDIFF('$date2','$date1')");
-
Have tried the above and not sure if it worked as I can't get at the result. The result is displaying as
Resource id #5
However if I:
$differenceRow= mysql_fetch_array($difference);
echo "the difference is $differenceRow[0]";//also tried 1
Then the output is:
Array
-
Are the values Unix TIMESTAMPs or are they Mysql TIMESTAMPs?
And datediff() will do what you want -
DATEDIFF(expr1,expr2)
DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
-> -31
And why not just use a single query? There is no point in executing a query to get two values, then put them back into another query just to use a mysql function to calculate the difference. Put the mysql function(s) into the first query.
They are mysql time stamp values
so am I correct that this is what you mean if written in php:
$date1 = $row_job_req_set['bricklayer_from_date'];
$date2 = $row_job_req_set['bricklayer_to_date'];
$difference = mysql_query("SELECT DATEDIFF($date2,$date1)");
echo "the difference is $difference";
would output the following
the difference is 4 (or whatever number it is for given dates)
-
I have been trying to find a complete example of a php page running a mysql database calculating the difference between 2 timestamp fields in days. I have concocted a bit of code but it still does not work. Any help would be appreciated
from_date and to_date are both TIMESTAMP's
mysql_select_db($database_myconnect, $myconnect);
$query_job_req_set = sprintf("SELECT * FROM job_request WHERE job_id = %s", GetSQLValueString($colname_job_req_set, "int"));
$job_req_set = mysql_query($query_job_req_set, $myconnect) or die(mysql_error());
$row_job_req_set = mysql_fetch_assoc($job_req_set);
$totalRows_job_req_set = mysql_num_rows($job_req_set);
$date1 = $row_job_req_set['bricklayer_from_date'];
$date2 = $row_job_req_set['bricklayer_to_date'];
$queryDateDiff = sprintf("SELECT TO_DAYS($date1) - TO_DAYS($date2) FROM job_request WHERE job_id=%s", GetSQLValueString($colname_job_req_set, "int"));
$date_req_set = mysql_query($queryDateDiff, $myconnect) or die(mysql_error());
$totalRows_date_req_set = mysql_num_rows($date_req_set);
echo "number of rows $totalRows_date_req_set";
$date_row = mysql_fetch_row($date_req_set);
echo " The result is $date_row[0]";
securing phpMyAdmin - localhost and ssh OR 2 factor authentication with internet accessible url?
in Apache HTTP Server
Posted · Edited by whitedragon101
I got this reply back on another forum
Would this not be secure? They would have to know the page address and then crack a long password. Isn't that what would be required to break the ssh? i.e know the port then brute force the secret key.
If the index was disabled on htaccess wouldn't the page address have to be brute forced as well?
e.g www.mywebsite.com/ri13t673gr672tf762g7676f2i37fggreg23i7f623/index.php
(Only asking as this way seems easier, but if its not secure I'll go ssh)