Jump to content

kickstart

Staff Alumni
 View Profile  See their activity

  • Posts

    2,707

  • Joined

  • Last visited

 Content Type 

Posts posted by kickstart

    SELECT query - apply LIMIT condition to unique items only

    in MySQL Help

    Posted

    I have a database made up of website addresses and company names. Everytime a user submits a company name in a form they are also asked to submit the website address. (this is to make sure that the websites are automatically kept up to date).

     

    So whenever I need to find the latest website address for a company I just refer to the match with the latest timestamp.

     

    In that case I would have a select for company and the max date for that name. Something like this (made up tables and columns):-

     

    SELECT Company, MAX(Date_Added) FROM CompanyWebSites

     

    Then JOIN that back against the company web sites table to get the rest of the details

     

    SELECT Sub1.Company, Sub1.LatestDateAdded, WebSiteName
FROM SELECT Company, MAX(Date_Added) AS LatestDateAdded FROM CompanyWebSites) Sub1
INNER JOIN CompanyWebSites
ON Sub1.Company = CompanyWebSites.Company AND sub1.LatestDateAdded = CompanyWebSites.Date_Added

     

    Assuming that date added is unique for any companies web sites then that should give you what you want.

     

    It would probably be more efficient to have a table of companies as well and use a unique numeric company id rather than the name.

     

    All the best

     

    Keith

    2 tables join together

    in MySQL Help

    Posted

    Hi

     

    That is more a php question than a MySQL question.

     

    If you want one per page then probably best to either bring them all back and store them in an array (maybe in a session variable given the numbers will be limited) and page through that array, or you use a LIMIT clause on the select to select the one you want, and on the next page you select the other one.

     

    All the best

     

    Keith

    DOUBLE INSERT

    in PHP Coding Help

    Posted

    Hi

     

    You are displaying data before you attempt the insert, hence needing to do the refresh to display it. And a refresh probably will resubmit the form data so inserting another row

     

    All the best

     

    Keith

    mysql_fetch_array help. displaying only selected records

    in MySQL Help

    Posted

    Hi

     

    Would depend on how you want to exclude that record.

     

    SELECT * FROM user ORDER BY $sort

     

    brings back everything.

     

    You can use a WHERE clause to limit the rows coming back. For example the following would only bring back rows where the department is 'It Div'

     

    SELECT * FROM user WHERE Department = 'IT Div.' ORDER BY $sort

     

    Or you can checking multiples with:-

     

    SELECT * FROM user WHERE Department = 'IT Div.' OR Department = 'Finance' ORDER BY $sort

     

    OR

     

    SELECT * FROM user WHERE Department IN ('IT Div.', 'Finance') ORDER BY $sort

     

    You can check other columns in the same way.

     

    If you just want to ignore the first record brought back (which will depend on the sort order) then you can use LIMIT:-

     

    SELECT * FROM user ORDER BY $sort LIMIT 1, 9999999

     

    (where 9999999 is a large number bigger than the number of records you will every possibly bring back).

     

    Please note that in your code you are using an unescaped variable from a for as a column in the sort clause. This is dangerous as it leaves you wide open to an SQL injection attack (where someone puts malicious SQL code in the data sent to your script). If you want to pass in the sort column I suggest you use something like:-

     

    switch ($_POST['sort'])
{
case 'Department' :
$sort= 'Department';
break;
case 'Lastnamecase' :
$sort = 'Lastnamecase';
break;
case 'Address':
$sort = 'Address';
break;
default :
$sort = 'Department';
}

     

    This way only fields you really want to allow can be used for the sort.

     

    Or if you can put up with the readability issues of having sort columns defined by the column number:-

     

    $sort = ((is_numeric($_POST['sort'])) ? intval($_POST['sort']) : 1);

     

    but it isn't that useful (prevents nasties easily, but will still cause problems if there is no column with that number, such as column 0).

     

    All the best

     

    Keith

    2 tables join together

    in MySQL Help

    Posted

    Hi

     

    Idea is that you have your table of listings , and your table of agents and one of categories. Probably all with an auto increment primary key.

     

    Then you have a table to link listings to agents:-

    Id

    ListingId

    AgentId

     

    Same for categories

    Id

    ListingId

    CategoryId

     

    This was to find the agents for a listing you join the listings table with the listings / agent link table and then join that with the agent table to get the details.

     

    This means that you can have as many agents as you want for each listing without having to change the table designs to cope. Also means that you are joining on a single field.

     

    Furthermore, if you wanted to know all the categories for listings an agent was involved in you only have to join agents to listings via a couple of joins, rather than joining on 2 columns (which becomes even nastier when you change it to, say, 10 agent columns in the future)

     

    All the best

     

    Keith

    Finding every Nth record in a database with a non-incremental index.

    in MySQL Help

    Posted

    In terms of the last row, just UNION it in if it's not % 120.

     

    Can do, but then you land up with a UNIONed statement which is having to do a JOIN from a select to get the MAX and a select to get the details, or instead unioning against a SELECT with an ORDER BY and a LIMIT clause.

     

    It is 6 of one and half a dozen of the other. But personally I prefer the single query for both ends of the range on a row.

     

    It is a pity that you can't put the sequence number generating select in a VIEW (either SET before or within a subselect). If you could this would simplify my code dramatically.

     

    All the best

     

    Keith

    PHP, cURL and loggin on to Joomla

    in PHP Coding Help

    Posted

    Hi

     

    I am trying to knock up a script to create a userid and log someone into a Joomla based site. The script is running on the same server as the site (and is legitimate, not nefarious).

     

    I can create an ID OK, and I can retrieve the login page, scrape the details (including the token) and submit the form.

     

    When I do this the user is logged in according to the Joomla sessions table, but it has also created a 2nd guest session there. Navigating to the Joomla site (either manually or doing a header redirect) just takes you to the site as though you are not logged in.

     

    Code as it stands (and code to deal with being passed user ids and passwords will change to be at least vaguely secure, just trying to get things to work now)

     

    <?php
session_start();
session_regenerate_id();

require("configuration.php");

$ConfigDetails = new JConfig();

$dbms = $ConfigDetails->dbtype;
$dbhost = $ConfigDetails->host;
$dbname = $ConfigDetails->db;
$dbuser = $ConfigDetails->user;
$dbpasswd = $ConfigDetails->password;

$salt = 'somesalt';

$url = "http://localhost/joomla/index.php";
$IncomingUid = $_REQUEST['uid'];
$IncomingName = $_REQUEST['name'];
$IncomingPassword = $_REQUEST['pwd'];
$IncomingEmail = $_REQUEST['email'];


// Make the database connection.
$SurveyConn = mysql_connect($dbhost,$dbuser,$dbpasswd);

mysql_select_db($dbname,$SurveyConn) or die(mysql_error());

$sql = "SELECT * FROM ".$ConfigDetails->dbprefix."users WHERE username = '".mysql_real_escape_string($IncomingUid)."'";

$rs = mysql_query($sql) or die(mysql_error());

if ($row = mysql_fetch_assoc($rs)) 
{
if ($IncomingPassword == $row['password'])
{
}
}
else
{
$PasswordEncrypted = md5($IncomingPassword.$salt).':'.$salt;
$sqli = "INSERT INTO ".$ConfigDetails->dbprefix."users (id, name, username, email, password, usertype, block, sendEmail, registerDate, lastvisitDate, activation, params) VALUES(NULL, '".mysql_real_escape_string($IncomingName)."','".mysql_real_escape_string($IncomingUid)."','".mysql_real_escape_string($IncomingEmail)."','".mysql_real_escape_string($PasswordEncrypted)."','deprecated',0,1,NOW(), NOW(), '', '')";
$rs = mysql_query($sqli) or die(mysql_error()." $sqli");
$sqli = "INSERT INTO ".$ConfigDetails->dbprefix."user_usergroup_map (user_id, group_id) VALUES(".mysql_insert_id().", ";
$rs = mysql_query($sqli) or die(mysql_error()." $sqli");
}

$agent = "'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.6) Gecko/20060728 Firefox/1.5.0.6'";

$c1 = curl_init();
curl_setopt($c1, CURLOPT_URL, $url );
curl_setopt($c1, CURLOPT_SSL_VERIFYPEER, FALSE );
curl_setopt($c1, CURLOPT_RETURNTRANSFER, TRUE );
curl_setopt($c1, CURLOPT_VERBOSE, 1);
curl_setopt($c1, CURLOPT_COOKIEJAR, 'cookie.txt');
curl_setopt($c1, CURLOPT_COOKIEFILE, 'cookie.txt');
curl_setopt($c1, CURLOPT_USERAGENT, $agent );
curl_setopt($c1, CURLOPT_HEADER, TRUE );
curl_setopt($c1, CURLOPT_REFERER, $url1);
curl_setopt($c1, CURLOPT_POST, 1);

$html = curl_exec($c1);	

$dom = new DOMDocument();

$FormFieldsArray = array();
if (@$dom->loadHTML($html)) 
{
// yep, not necessarily valid-html...
$xpath = new DOMXpath($dom);

$nodeListInputs = $xpath->query('//input');
if ($nodeListInputs->length > 0) 
{
	$FormFieldsArray = array();
	for ($i=0 ; $i<$nodeListInputs->length ; $i++) 
	{
		$nodeInput = $nodeListInputs->item($i);
		$name = $nodeInput->getAttribute('name');
		$value = $nodeInput->getAttribute('value');
		$FormFieldsArray[$name] = $value;
	}
}
}
else 
{
// too bad...
}

if (count($FormFieldsArray) > 0)
{	
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url );
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE );
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE );
curl_setopt($ch, CURLOPT_VERBOSE, 1);
curl_setopt($ch, CURLOPT_COOKIEJAR, 'cookie.txt');
curl_setopt($ch, CURLOPT_COOKIEFILE, 'cookie.txt');
curl_setopt($ch, CURLOPT_USERAGENT, $agent );
curl_setopt($ch, CURLOPT_HEADER, TRUE );
curl_setopt($ch, CURLOPT_REFERER, $url1);

// POST fields
$postfields = array();
foreach($FormFieldsArray AS $FormFieldName=>$FormFieldValue)
{
	switch ($FormFieldName)
	{
		case 'username' :
			$postfields['username'] = urlencode($IncomingUid);
			break;
		case 'passwd' :
			$postfields['passwd'] = urlencode($IncomingPassword);
			break;
		case 'password' :
			$postfields['password'] = urlencode($IncomingPassword);
			break;
		default :
			$postfields[$FormFieldName] = $FormFieldValue;
			break;
	}
}

curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postfields);

$ret = curl_exec($ch);	

// Get logged in cookie and pass it to the browser
preg_match('/^Set-Cookie: (.*?);/m', $ret, $m);
$cookie = explode('=', $m[1]);
setcookie($cookie[0], $cookie[1]);
header("location:  ".$url);
}
//echo $ret;
?>

     

    Any ideas?

     

    All the best

     

    Keith

    How to check two tables

    in MySQL Help

    Posted

    Hi

     

    While in every way agreeing with PFMaBiSmAd, you could do it with:-

     

    SELECT 'Student' AS LoginType, students.student_id, students.first_name, students.user_level
FROM students
WHERE students.email='$e' AND students.pass=SHA1('$p'
UNION
SELECT 'Staff' AS LoginType, staff.staff_id, staff.first_name, staff.user_level 
FROM staff 
WHERE staff.email='$e' AND staff.password=SHA1('$p'))

     

    That should give you the row along with whether it is a student or a staff table row.

     

    What your current code appears to be doing is a cross join, giving you every combination of rows from the 2 tables (ie, 100 students and 100 staff would generate 10000 rows), and then finding those where the email and password match (so if one student matched you would get 100 rows for that student, one for each member of staff).

     

    All the best

     

    Keith

    2 tables join together

    in MySQL Help

    Posted

    Hi

     

    I would use something like this:-

     

    SELECT *
FROM listings
INNER JOIN agents
ON agents.name = listings.agent
WHERE listings.category2 = 'lake' or listings.category = 'lake')
UNION 
SELECT *
FROM listings
INNER JOIN agents
ON agents.name = listings.agent2
WHERE listings.category2 = 'lake' or listings.category = 'lake')
ORDER BY listings.timeStamp DESC

     

    All the best

     

    Keith

    can i INNER JOIN 3 tables

    in MySQL Help

    Posted

    Hi

     

    Probably yes, especially as I presume you need some way of looking up which cars already have an order for which day.

     

    But that table of orders would contain the id fields of the records on other tables that it refers to, not the values themselves.

     

    All the best

     

    Keith

    PHP While loop inside SQL Insert

    in MySQL Help

    Posted

    Hi

     

    You use ON DUPLICATE KEY UPDATE within an insert statement.

     

    For example, assuming UserID and eth_ID is a unique key:-

     

    INSERT INTO user_ethnicity (UserID, eth_ID, Value) 
VALUES ('$user_ID', '$NewID', '$Value')
ON DUPLICATE KEY UPDATE Value=VALUES(Value)

     

    All the best

     

    Keith

    Finding every Nth record in a database with a non-incremental index.

    in MySQL Help

    Posted

    You could include both in your HAVING clause.

     

    You could, but then you land up with 2 rows for each page range, rather than a single row with a column for each of the start and end points of the range.

     

    If you want to use HAVING and have 2 rows:-

     

    $query ="Set @counter = -1; SELECT customer_index, customer_name, customer_timestamp, @counter := @counter + 1 FROM Customers HAVING ((@counter % 120 = 0) OR (@counter % 120 = 119))";
$result=mysql_query($query) or die ("Query failed.");

     

    Note that the above won't give you the end of the last page unless the total number of records is exactly divisible by 120.

     

    All the best

     

    Keith

    PHP While loop inside SQL Insert

    in MySQL Help

    Posted

    Hi

     

    This is the way I would do it:-

     

    and what your saying is, I should then have one table for when these are checked, like

     

    USERID------------CheckboxID------------CheckboxOn
1st user---------------1---------------------------0
1st user---------------2---------------------------1
2nd user--------------1---------------------------1
2nd user--------------2---------------------------1

     

    Unique key on userid and checkbox id, and when the value changes use an insert with ON DUPLICATE KEY UPDATE.

     

    All the best

     

    Keith

    Creating League Table

    in MySQL Help

    Posted

    Hi

     

    Further to Fenways comment, if you have a single row for the result of a match it makes finding a teams score more difficult as you need to join against either column.

     

    Ie, if you have a table of teams

    TeamId

    TeamName

     

    And a table of scores

    ScoreId

    HomeTeamId

    AwayTeamId

    HomeTeamScore

    AwayTeamScore

     

    If you want to know all the scores from matches a team has played you land up needing 2 queries unioned together:-

     

    SELECT TeamName, HomeTeamScore, AwayTeamScore
FROM teams
INNER JOIN scores
ON teams.TeamId = scores.HomeTeamId
UNION
SELECT TeamName, AwayTeamScoe, HomeTeamScore
FROM teams
INNER JOIN scores
ON teams.TeamId = scores.AwayTeamId

     

    If you have 2 rows on the score table for each match, one for each team you save the need for a UNION.

     

    Another option is to create a view based on the scores table to return both combinations of each row.

     

    All the best

     

    Keith

    A better way to get MYSQL result??

    in MySQL Help

    Posted

    Hi

     

    Further to the above, if you want the counts then something like this:-

     

    SELECT subscriber.s_id, subscribers.s_email, COUNT(Newsletter_Subscribers)
FROM subscribers 
INNER JOIN newsletter_subscribers 
ON newsletter_subscribers.id_subscriber != subscribers.s_id
GROUP BY subscriber.s_id, subscribers.s_email

     

    Although logically it seems strange. You appear to be getting a list of subscribers and then getting a count of all the newsletter subscribers who are not that subscriber.

     

    All the best

     

    Keith

    can i INNER JOIN 3 tables

    in MySQL Help

    Posted

    Hi

     

    Something like this:-

     

    SELECT e.name AS event, t.name AS town, v.name AS vehicle, vt.name AS vehicleType, vt.style AS vehicleStyle, p.base AS price
FROM EVENTS AS e
JOIN towns AS t ON e.town_id = t.id 
JOIN vehicles AS v ON e.id = v.id
JOIN vehicle_type AS vt ON v.type = vt.id
JOIN price AS p ON v.price_id = p.id
WHERE v.passengers >= 1 
AND p.townID = 1 
AND p.eventID = 1

     

    All the best

     

    Keith

    File Management

    in PHP Coding Help

    Posted

    Hi

     

    That is more a php than mysql issue.

     

    File uploads are not that complicated and there are loads of tutorials online. Essentially you have a form with enctype='multipart/form-data' and an input field of type file. This lands up in the $_FILES array in php, which is an array (of files) of arrays (of details of a file). You loop through that, checking the files a required and then move the file from the tmp_name array key to where you actually want it stored (which should be outside any web accessible directory).

     

    You then have a script to produce the file on demand, which uses something like the following:-

     

    header('Content-Length: ' .filesize($FullFilePath));
header("Content-Type: image/jpeg");
readfile($FullFilePath);

     

    to set the length of the output, the file type of the output and then copy the saved file out.

     

    Note that you can (and probably should) store the files with a random name, and have a database table entry linking the random name to the real name (means duplicate names are not a problem, and also means that even if someone does get access to the files directly they are delayed a bit by meaningless names).

     

    All the best

     

    Keith

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.