8 column get result from 1 column[mysql]

[hayato1]

:😛ROBLEMS::SOLVED::April 18,2004
http://ragnarok.vaxva.com:8080/
with the help of bastein, keep answering to my Q.

sorry everyone,
I'm really stuck with this question for about 4 weeks

i trying my luck to seek help here

I got table A and table B
table A have 8 ID column
table B have 1 ID column and 1 name column

table A 's 8 ID <=> table B 's ID display name according to ID

<?php
$sql = "SELECT *
FROM `ro_item` i
LEFT JOIN `ro_mob` r
ON  r.mob_drop1id = i.item_id
WHERE 1 AND r.mob_name";
$sql_result = mysql_query($sql,$connect) 
or die("Couldn't execute query."); 
while ($row = mysql_fetch_array($sql_result)) {
$mob_drop1id = ($row['mob_drop1id'] = $row['item_name']);
?>

[bastien]

sounds like alias' and specific column selects might be helpful here
where each column given a name that corresponds to the data in the field...then the column alias is used when referencing the field

[hayato1]

bastien, Thank you for your advise
out of so many board i posted at last i got a answer here

but you can see,
from my code you could tell I'm pretty new to this,
and if i know the way i won't be stuck

can you just show me how i could do it please
just the 1st step, so I know where do i start from.

Hope this request hasnt been too much

[hayato1]

i Used this and they end up couldnt recongnize who is who and display same result " actually they are different"

$sql = "SELECT i.item_id, i.item_name,
r.mob_drop1id AS item1,
r.mob_drop2id AS item2
FROM ro_mob r
LEFT JOIN ro_item i
ON r.mob_drop1id = i.item_id
WHERE 1

while ($row = mysql_fetch_array($sql_result)) {
$item2 = ($mob_drop2id = $row['item_name']);
$item1 = ($mob_drop1id = $row['item_name']);

<td><?echo"$item1"?></td>
<td><?echo"$item2"?></td>

[bastien]

while ($row = mysql_fetch_array($sql_result)) {
$item2 = ($mob_drop2id = $row['item_name']);
$item1 = ($mob_drop1id = $row['item_name']);

should be

while ($row = mysql_fetch_array($sql_result)) {
$item2 = ($mob_drop2id = $row['item2']);
$item1 = ($mob_drop1id = $row['item1']);

[hayato1]

thanks for replying,bastien
but i tried it fetching the original ID instead of 'item_name'
<?
$sql = "SELECT r.*,i.item_name, i.item_id
r.mob_drop1id AS item1,
r.mob_drop2id AS item2
FROM ro_mob r
LEFT JOIN ro_item i
ON r.mob_drop1id = i.item_id
WHERE 1
ORDER BY $catergory $sort LIMIT $j,$n";
$sql_result = mysql_query($sql,$connect)

while ($row = mysql_fetch_array($sql_result)) {
$item2 = ($mob_drop2id = $row['item2']);
$item1 = ($mob_drop1id = $row['item1']);
?>

<td><?echo"$item1"?></td>
<td><?echo"$mob_drop1per"?>%</td>
<td><?echo"$item2"?></td>
<td><?echo"$mob_drop2per"?>%</td>

i see seems like now dropid* doesnt told to get item_name
and theres only 1 column of (item_name & item_id) in table 2
theres 8 column ID trying to refer.

sad.....will I ever suceed?
The "not for human read page is here, where shows ID and sulfer wont know what is that ID"
http://www.vaxva.com:8080/game/rov/db/mob.php

[bastien]

while ($row = mysql_fetch_array($sql_result)) {
$item2 = ($mob_drop2id = $row['item2']);
$item1 = ($mob_drop1id = $row['item1']);
?>

<td><? echo $item1;?></td>
<td><? echo $item2;?></td>

[hayato1]

from that it still displaying ID not name
is item1 and item2 a varible?
because they are not column name

but if again at SQL SELECT

r.mob_drop1id AS item1
change to this
i.item_name AS item1
will get the name but however it still a row only
not 8

i think somwhere join is missing
between (table_a 8ID) and (table_b 1ID) to get name from table_b

[bastien]

can you dump your table stucture and some sample data to me (either here or email to [e-mail]

[bastien]

for one text element in the search form

<?
if(isset($_POST['search']){
   $search =  $_POST['search'];
}

//seach item MUST be in ALL fields
$sql = "select * from tableName where
             ColumnName1 = '$search'
             AND ColumnName2 = '$search'
             AND ColumnName3 = '$search'
             AND ColumnName4 = '$search'
             AND ColumnName5 = '$search'
             AND ColumnName6 = '$search' 
             AND ColumnName7 = '$search'
             AND ColumnName8 = '$search'";

//seach item CAN be in ANY field(s)
$sql = "select * from tableName where
             ColumnName1 = '$search'
             OR  ColumnName2 = '$search'
             OR  ColumnName3 = '$search'
             OR  ColumnName4 = '$search'
             OR  ColumnName5 = '$search'
             OR  ColumnName6 = '$search' 
             OR  ColumnName7 = '$search'
             OR  ColumnName8 = '$search'";


}

to MATCH partial parameters (ie Bill, Billy) use LIKE instead of =.
To match from beginning of word use the % after the seach element

$sql = "select * from tableName where
             ColumnName1 like '$search%'

To match from end of word use the % before the seach element

$sql = "select * from tableName where
             ColumnName1 like '%$search'

and the where the search term is in the middle

$sql = "select * from tableName where
             ColumnName1 like '%$search%'

I post here in most of the forums..

[bastien]

yes, you can have multiple search items at one time. Just create a different search text field for each one....you could either name them search1-search8 (and label them according to the correct elements(ie first name= search1, last name= search2) or just give them each a unique name

[hayato1]

Last I'm Back to thanks
bastein
All problems are solved

Thanks for all along answering my newbie Question
http://ragnarok.vaxva.com:8080/