Need help about Mysql

themaster · Aug 30, 2008

Hi,
I need help about selecting data on a special condition in MySQL.

Here the details what I m trying to do;
I have two table (table1 and table2). And I want to compare a column of each table. This columns are wordlist (at table1) and userword (at table2).

What I want to do is comparing this two column in order to see which data at wordlist also in userword at table2. And selecting the data on userword (at table2) which is the same with at wordlist (at table1).

Hope what i mean is clear.

Any help pls..
Thanks..

RageD · Aug 30, 2008

Are you looking for just a MySQL Query or like a way to do this in something like PHP?

Also, will it be an array of data? If you're checking against db1 and the initial user input goes to table2, you can grab information at table 2 with for() or better yet, while(); and then use the variable created to check the same information in table1

-RageD

Here's something that might work. Have to leave so haven't had a chance to test but here it is:

PHP:

<?php
// I'll setup a mini-webpage. Here goes.
if(!$page OR !$_GET['page'])
{
	Header("Location: ?page=default");
}
$page = $_GET['page'];
switch($page)
{
	case "default":
?>
<HTML>
<BODY>
<form name=checkDB method=POST action="?page=check">
User Input: <input type=text name=userInput>
<input type=submit value=Check>
</form>
</BODY>
</HTML>
<?php
	break;
	case "check":
		// MySQL DB Connect Info
		mysql_connect("localhost","USER","PASS"); // Replace with proper info
		mysql_select_db("TABLE2"); // Replace with table2 name

		// Set the query and result
		$query = "SELECT * FROM field WHERE identifier='".$_POST['userInput']."'";

		// Run the loop, grab the data
		while($row = mysql_query($query))
		{
			$data[] = $row['dataname']."<br />"; // provided that it is an array of data
		}
		
		// Make the array normal for us or we can try a for but I'm not sure exactly what you need
		$data = implode($data);

		// 2nd Query
		mysql_select_db("TABLE1"); // Checking it against the other table now
		$query = "SELECT * FROM field WHERE data='".$data."'";
		
		// Next loop
		while($row = mysql_query($query))
		{
			$data2[] = $row['dataname']."<br />"; // again, [] assumes it is an array of data
		}
	
		// Turn array into string
		$data2 = implode($data2); // Unnecessary if only a string still

		print "<b><u>Data Table 1 Information</u></b><br />".$data2."<br /><br />
		       <b><u>Data Table 2 Information</u></b><br />".$data."";
	break;
}
?>

-RageD

themaster · Aug 30, 2008

Thanks you so much for your help RageD.

Actually I will prefer to use just an MySQL command to work on directly at MySQL prompt at ssh. Because I have 16k data at table1 and 160k data at table2. It will be very hard to work on that much big comparison on php I think.

I want to check if each cell at table1 have a same value in somewhere in table2. Let say there is a "valueqqq" (string type) in table1 column1, it will check table2 column2 and if there is "valueqqq" too it will select that cell from table2. In this way I will test each that 16k data in table1 with 160k data in table2. And I will have list of data that is duplicate for these two tables.

For further, I want to add this selected data in another table let say table3 column3..

It will compare two dataset from table1 and table2. And at the final stage I will have something like this:

column3
valueqqq1
valueqqq2
valueqqq3
valueqqq4
valueqqq5
valueqqq6
so on

All of the variables at column3 have duplicate at column1 and colum2.

I think I cant explain the stuation clearly because of my English problem :S

Edit: Now I m thinking to combine column1 and column2 (assume there is no duplicates in each column respectively) and check the duplicates and that final column. I think this will be easier but may be not most effective.

And again I will have same problem about inserting that duplicates in other column I think :S

baxter · Aug 30, 2008

you will probably need php to do the loop through and insert the duplicates as for the mysql from what I understand your looking for something like this?

Code:

SELECT t1.*
FROM table1 as t1
LEFT JOIN table2 as t2 ON (t1.column1=t2.column2)
WHERE t1.column1=t2.column2

dman_2007 · Aug 30, 2008

Here's what you need, if i understood you correctly :

Code:

INSERT INTO table3 (column3) SELECT t1.column1
                             FROM table1 t1
                                   INNER JOIN 
                                     table t2
                                        ON t1.column1 = t2.column2;

this query will find and fill column3 of table3 with data values which are present in both column1 of table1 and column2 of table2.

One more thing, if the column2 in table2 contains duplicate values itself (for example, multiple instances of string 'ab'), something which you don't want to have in column3 of table3 then use distinct in the query given above.

themaster · Aug 30, 2008

Thanks you so much baxter and dman_2007

I will try these. I hope the big data file wont be problem.

Need help about Mysql

Master of the WebEstablished Member

VIP Member

Master of the WebEstablished Member

Established Member

VIP Member

Master of the WebEstablished Member

Similar threads

We're social

Creative-Power.com

LXFF.Com

strivecash.com

casinopaylater.com

cashlesspaycheck.com

C-R-M.com

fup.io

betfind.net

OpenWallet.ca

CapitalFunders.com

Pinned

Appreciation

Agreement

Answers

Relevance

Reaction

Status

Feeling