Posts by urtrivedi

Please do not use html code only keep php tags whatever i have given below.

Also there is one error in line $row=$mysql_fetch_array($result);
remove $ sign from the begining of mysql_fetch_array
so correct line is

$row=mysql_fetch_array($result);

following code is correct code

<?php			
include("config.php");    
$result=mysql_query("select SiteID,MFR from eims_info where SiteID='{$_REQUEST['course']}'");    
$row=mysql_fetch_array($result);    
echo $row['MFR']; //return mf value for requested code;
?>

REMOVE ALL HTML CODE your script should return only value of mf

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

xmlhttp.open("GET","getmf.php?course="+str,true);

here you are passing course and in your getmf you are using Requst[a]
so you should use
....where SiteID='{$_REQUEST}'

you open browser and run yourserver/getmf.php?course=8645

now see what the browser is returning, is it giving curret value for 8645 or not.

also in getmf.php file delete extra space and lines before <?php tag and after ?> tag

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

You refer to my last post on the first page of this thread, code is using AJAX.

Now if you want to do it in above code. then this code will set 5500 array elements in javascript, which is not good practice. Also you need to set database connection before executing the query. also have you changed. mytable to the actual tablename in your script.

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

I am using basic ajax. Now you have to create one php file getmf.php in the same folder where your current file is saved. copy following code to it. set database connection properly

<?php
	//set data base connection
    $result=mysql_query("select code,mf from mytable where code='{$_REQUEST['code']}'");
    $row=$mysql_fetch_array($result);
    echo $row['mf'];//return mf value for requested code;
?>

then add new javascript function and other changes as given below to current file. Now if you are not comfortable with the code then I suggest you to learn some ajax tutorials and then try to solve your problem

<script lang='javascript'>
function findmf(str)
{
	if (str=="")
	{
		document.getElementById("a").value="";
		return;
	}  
	if (window.XMLHttpRequest)
	{// code for IE7+, Firefox, Chrome, Opera, Safari
		xmlhttp=new XMLHttpRequest();
	}
	else
	{// code for IE6, IE5
		xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
	}
	
	xmlhttp.onreadystatechange=function()
	{
		if (xmlhttp.readyState==4 && xmlhttp.status==200)
		{
			document.getElementById("a").value=xmlhttp.responseText;
		}
	}
	xmlhttp.open("GET","getmf.php?code="+str,true);
	xmlhttp.send();
}

function calculate()
{
 //e is previous
 //f is current
 //g is new is f-e
 //code is code
 //a is mf
 
 	previous=parseFloat(document.getElementById("e").value);
 	curr=parseFloat(document.getElementById("f").value);

	if(!isNaN(previous) && !isNaN(curr) )
	{
	 	if(curr>=previous)
			document.getElementById("g").value=curr-previous*(parseFloat(document.getElementById("a").value));
		else
			document.getElementById("g").value="";
	}
	else
	{
		document.getElementById("g").value="";
	}
}
</script>


<body>
<form>
code <input type="text" name="code" id="code" value=""  class="input"    onblur='javascript:findmf(this.value);' > <br>
a <input type="text" name="a" id="a" value=""  class="input"   readonly> <br>

e <input type="text" name="e" id="e" value="340"  class="input" onblur='javascript:calculate();' > <br>
f <input type="text" name="f" id="f" value=""  class="input"  onblur='javascript:calculate();' > <br>
g <input type="text" name="g" id="g" value=""  class="input"  readonly >
</form>

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

You mean your code-mf table is having 5500 rows.

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Go through the follwing code carefully. I have not used ajax here. Its simple php/javascript. I am assuming that your code,mf table is very small so i am storing its values in a javascript array, and using it for calculation. If your table is very large then u must learn ajax for retriving values from mysql. Make necessary changes whereever application

<script lang='javascript'>
arrmfrates = new Array();
<?php 
	/*here find code, mf from database and create one javascript array*/
	
	/* following loop will create javacript asscoicate array as following
	arrmfrates["c8620"] = 2;
	arrmfrates["c8621"] = 1;
	arrmfrates["c8645"] = 5;
	*/
    
    $result=mysql_query("select code,mf from mytable");
	while ($row=$mysql_fetch_array($result ))
	{
		
		echo "\n arrmfrates['c".$row['code']."] = ".$row['code'].";";

	}
?>

/*this function will be called when user enters code, to find mf value stored in javascript array, this mf value is copied to "a" field of database*/
function findmf(code)
{

	if(arrmfrates['c'+code]!=undefined)
		document.getElementById("a").value=arrmfrates['c'+code];
	else
		document.getElementById("a").value="";
}

function calculate()
{
 //e is previous
 //f is current
 //g is new is f-e
 //code is code
 //a is mf
 
 	previous=parseFloat(document.getElementById("e").value);
 	curr=parseFloat(document.getElementById("f").value);

	if(!isNaN(previous) && !isNaN(curr) )
	{
	 	if(curr>=previous)
			document.getElementById("g").value=curr-previous*(parseFloat(document.getElementById("a").value));
		else
			document.getElementById("g").value="";
	}
	else
	{
		document.getElementById("g").value="";
	}
}
</script>


<body>
<form>
code <input type="text" name="code" id="code" value=""  class="input"    onblur='javascript:findmf(this.value);' > <br>
a <input type="text" name="a" id="a" value=""  class="input"   readonly> <br>

e <input type="text" name="e" id="e" value="340"  class="input" onblur='javascript:calculate();' > <br>
f <input type="text" name="f" id="f" value=""  class="input"  onblur='javascript:calculate();' > <br>
g <input type="text" name="g" id="g" value=""  class="input"  readonly >
</form>

Re: Get the result of two inupts into 3rd inut field

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

no need to keep it in hidden field.
You may query database before script tag and find value in variable $mfvalue.
then echo value of $mfvalue in your javascript as i have shown in abouve post.

Re: Get the result of two inupts into 3rd inut field

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

you need to change as code given below

<?php
// find multiplier here 
$mfvalue=9;
?>
<script lang='javascript'>
function calcualte()
{
.
.
.
.
if(curr>=previous)
	document.getElementById("g").value=(curr-previous)*(<?php echo $mfvalue;?>);

.
.
.
}

Re: Get the result of two inupts into 3rd inut field

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Ya ayesha you may ask.

Re: Get the result of two inupts into 3rd inut field

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Dear Raja
Some time if you parse string to float it my give Nan (NOT a number) error, to avoid such error I have used isNaN function.

Dear Ayesha
Dont worry, this code will give you result in g field

Re: Get the result of two inupts into 3rd inut field

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

g field is calculated field, user may not change it so onblur='javascritp:calculate();' is not required for g field. User is not going to type there anything.

I am not checking for browser compatibility anywhere, why do you feel so?

Re: Get the result of two inupts into 3rd inut field

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

some code was not required given in my previous post. following is the final one.
Add following javascript code before html body tag.

<script lang='javascript'>
function calculate()
{
 //e is previous
 //f is current
 //g is new is f-e
 	previous=parseFloat(document.getElementById("e").value);
 	curr=parseFloat(document.getElementById("f").value);
	if(!isNaN(previous) && !isNaN(curr) )
	{
	 	if(curr>=previous)
			document.getElementById("g").value=curr-previous;
		else
			document.getElementById("g").value="";
	}
	else
	{
		document.getElementById("g").value="";
	}
}
<script>

in your html part add onblur events as shown below in your text box elements

<input type="text" name="e" id="e" value="<?php echo $e; ?>"  class="input" onblur='javascritp:calculate();' /> 
<input type="text" name="f" id="f" value=""  class="input"  onblur='javascritp:calculate();' />

Re: Get the result of two inupts into 3rd inut field

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Add following javascript code before html body tag.

<script lang='javascript'>
function calculate()
{
 //e is previous
 //f is current
 //g is new is f-e
 	previous=parseFloat(document.getElementById("e").value);
 	curr=parseFloat(document.getElementById("f").value);
	if(!isNaN(previous) && !isNaN(curr) )
	{
	 	if(curr>=previous)
			document.getElementById("g").value=curr-previous;
		else
			document.getElementById("g").value="";
	}
	else
	{
		document.getElementById("g").value="";
	}
	var total = 0;      
	for (i = 1 ;i <= 15; i++)      
	{        
		if (i<10)
			labelname='amount_0'+i;
		else
			labelname='amount_'+i;
	
	
		if (!isNaN(parseFloat(document.getElementById(labelname).innerHTML)))
			total += parseFloat(document.getElementById(labelname).innerHTML);      
	}      
	
	document.getElementById(label2).innerHTML = total;
}
<script>

in your html part add onblur events as shown below in your text box elements

<input type="text" name="e" id="e" value="<?php echo $e; ?>"  class="input" onblur='javascritp:calculate();' /> 
<input type="text" name="f" id="f" value=""  class="input"  onblur='javascritp:calculate();' />

Re: MySQL referential integrity

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

If you want to refer to same table only once than you can do it in same table. If there are more than one references then you need to create another table with one to many relation.

You should always link to primary key, DO NOT USE ATTNAME. It is not a good practice.
you just refer to attid.

Databases mysql

nav33n commented: true! +6

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Re: appropriate sql statement for search

your query is absolutely fine. what error message you are getting.

Databases mysql

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

I hope this will work

function calculate(.......)
{
 .
 .
 .
 .
 
	var total = 0;      
	for (i = 1 ;i <= 15; i++)      
	{        
		if (i<10)
			labelname='amount_0'+i;
		else
			labelname='amount_'+i;
	
	
		if (!isNaN(parseFloat(document.getElementById(labelname).innerHTML)))
			total += parseFloat(document.getElementById(labelname).innerHTML);      
	}      
	
	document.getElementById(label2).innerHTML = total;
}

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

change you calculate function at the end as shown below

function calcuate(...........)
{
.
.
.
.

var total = 0;      
for (i = 1 ;i <= 15; i++)      
{        
	if (i<10)
	{
		total += parseFloat(document.getElementById('amount_0'+i).innerHTML);      
	}
	else
	{
		total += parseFloat(document.getElementById('amount_'+i).innerHTML);      
	}
}      
document.getElementById(label2).innerHTML = total;

}

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

post/attache your whole code.

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

var total=0;
for (i=1;i<=15;i++)
{
  total+=parseFloat(document.getElementById('label'+i).innerHTML); 
}
document.getElementById(label3).innerHTML = total;

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

at the end of calculate function you may add code

FUNCTION CALCULATE
{
.
.
.
.
     document.getElementById(label1).innerHTML = cost;

     document.getElementById(label3).innerHTML = parseFloat(document.getElementById(label1).innerHTML) + parseFloat(document.getElementById(label2).innerHTML);
}

Re: Sorting data asc and desc using a jump menu

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

You need to learn about "limit" clause of mysql

in user form

.
.
.

<input type=hidden name=page value=<?php echo $_REQUEST['page'];?> >
</form>

in query

$recperpage=10;
if ($_REQEUST['page']=="")
   $_REQEUST['page']=0;
$startfrom=$_REQEUST['page']*$recperpage;

$query=" SELECT * FROM kiniseis 
 order by date {$_REQUEST['dateorder']} 
 limit {$startfrom},{$recperpage} ";

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Re: PHP Array Problem

I have made few change in your code. I hope it will work as expected.

page 1, line 8

$i=0; 
while.....

page 1, line 10
changed only portion of check box element, removed id=checked[] and added value attribute

<input name="checked[]" type="checkbox" value="'.$i++.'" />

page 2, line 7 and 8

$model_name = $model[$checked[$i]];
	$model_prices = $model_price[$checked[$i]];

Re: Sorting data asc and desc using a jump menu

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

I dont know what you are asking for but I guess that you want to set selection box to the value selected by user. you may achive it using following code

<option value ="date.php?dateorder=asc" 
<?php echo ($_REQUEST['dateorder']=='asc')?"selected":"";?> >ASC</option>    
<option value ="date.php?dateorder=desc" 
 <?php echo ($_REQUEST['dateorder']=='desc')?"selected":"";?> >DESC</option>

Re: Sorting data asc and desc using a jump menu

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Re: Database Design Help

YOU may try following query

SELECT a.b_id, a.company_name AS name, a.ceo, a.founded, a.website, b.year, 
sum(case when b.year=2006 then b.earnings else 0 end ) y2006,
sum(case when b.year=2007 then b.earnings else 0 end ) y2007,
sum(case when b.year=2008 then b.earnings else 0 end ) y2008,
sum(case when b.year=2009 then b.earnings else 0 end ) y2009
FROM business a left outer JOIN earnings b on a.b_id=b.b_id 
group by a.b_id, a.company_name, a.ceo, a.founded, a.website ORDER BY (founded)

Web Development mysql php web-design

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Re: Database Design Help

To me it seems to be fine. You may continue using this design. You may join tables using following query

select a.company_id, a.company_name ,a.CEO, a.Founded, a.Website,  a.Share_Price ,
b.year, b.earning
from company a left outer join earnings b on a.company_id=b.company_id

Web Development mysql php web-design

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

<?php
  $query="select * from table order by date {$_REQUEST['qryorder']}";
.
.
.
.
.
.
?>

<form name=frm action='thispage.php'>
<select name=qryorder onchange='document.frm.submit()'>
<OPTION VALUE='asc'>Ascending
<OPTION VALUE='desc'>Descending
</SELECT>
</form>
.
.
.
.
.

<?php
  $query="select * from table order by date {$_REQUEST['qryorder']}";
.
.
.
.
.
?>

<a  href='thispage.php?qryorder=asc' >Ascending order</a>
<a  href='thispage.php?qryorder=desc' >Descending order</a>
.
.
.
..

Re: Syntax error driving me crazy

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

use == for comparision at line no 28
(++$i % 2) == 0

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Re: SQL Query - max count?

My mistake, here is the changed version

select  sellerid, count(*) nooftimes 
from sales 
where buyerid='$buid'
group by sellerid, buyerid 
order by buyerid,nooftimes desc, sellerid
limit 0,1

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Re: SQL Query - max count?

select  sellerid, count(*) nooftimes 
from sales 
where buyerid='$buid'
group by sellerid, buyerid 
order by sellerid,buyerid,nooftimes desc 
limit 0,1

$cnt[0] will return you sellerid
$cnt[1] will return you nooftime

Re: Insert calculated value to database

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

at your line number 13, add one line as shown below

$_POST['Belopp']=$avrundat;

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

I have written following code try. I have used only one form.
I hope this is what you are looking for.

<script lang='javascript'>
function joinname()
{   	
 	if(document.joinnameform.fullname.value!='')
 	{
	 document.joinnameform.fullname.value=document.joinnameform.fullname.value+',';	
	}
	document.joinnameform.fullname.value=document.joinnameform.fullname.value+ document.joinnameform.fname.value + ' ' + document.joinnameform.lname.value;
	document.joinnameform.fname.value ='';
	 document.joinnameform.lname.value='';
	
	//return true;
}
</script>

</head>

<form name=joinnameform id=joinnameform action='mycgiscript'> 
First name <input type=text name=fname id=fname >
Last name <input type=text name=lname id=lname ><br>
All names <input type=text name=fullname id=fullname readonly style='width:300px'><br>
<input type=button name=btnsub id=btnsub value='Add name to list' onclick='javascript:joinname()'>
<br><br><br><br>
<input type="submit" value="Submit" name=btnsubmit></p>



</form>
</html>

Re: updating to show display on existing, updated and un-update data.

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

I have set one flag before your while loop;
If while loop is executed then flag is true;
Now I have removed your else and written one if condition based on that flag, if option 1 and 2 is not shown then show option 3.

I hope this is what you are looking for.

$op1and2shown=false;
	while ($row = mysql_fetch_array($results, MYSQL_ASSOC))
             {
                 $op1and2shown=true;
		/////////////////////////////////
		///
		///	DISPLAY EXISTING DATA IN 
		///          TABLE FORM
		/////////////////////////////////
 
		/////////////////////////////////
		///
		///	DISPLAY UPDATE ON LATEST DATA
		///              IN TABLE FORM
		/////////////////////////////////
 
             } 
       } 

      If(!$op1and2shown)
          {
 
           echo $HW_dev_num;
 
		/////////////////////////////////
		///
		/// DISPLAY LATEST DATA NOT UPDATED
		///         
		/////////////////////////////////
 
           }

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

function joinname()
{   		
document.completenameform.fullname.value = document.joinnameform.fname.value + ' ' + document.joinnameform.lname.value;

return true;
}

Return true instead of fullname value.
I cannot understand why you are using two forms. You should keep fullname under joinnameform.

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

<input type=text name=fname id=fname >
<input type=text name=lname id=lname  >
<input type=text name=fullname id=fullname readonly >
<input type=submit name=btnsub id=btnsub  onclick='javascript:joinname()'>

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

<script lang='javascript'>
function joinname()
{
   document.frm.fullname.value= document.frm.fname.value+' ' +document.frm.lname.value;
}
</script>
<form name=frm id=frm >
<input type=text name=fname id=fname onblur='javascript:joinname()'>
<input type=text name=lname id=lname  onblur='javascript:joinname()'>
<input type=text name=fullname id=fullname readonly >
</form>

Re: updating to show display on existing, updated and un-update data.

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

If you say that your result is not showing 3rd option, that is else part of your if condtion, then it means that your all rows in row_export is satisfying your following condition if (trim($pib_bracket_num) !='' and trim($HW_design) !='' ), so it is priting only option 1 and 2.

I think there is no php/mysql problem. YOu need to rework your code. if data for 3rd option exists.

Re: How can I combine data from multiple rows into only one

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Please try the query again, it can not show multiple rows, when you use aggregate function without group by it will always return one or no row.

otherwise post your sample data with column names.

Re: Header redirect not working

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

exit after redirection

header('location: edit.php');	
exit;

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Re: Number of results found

User mysql_fetch_array() instead of mysql_fetch_assoc() in line no 4 of my code

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Re: Number of results found

you may add following code between line no 16 and 18

$countofrecs=mysql_query("SELECT count(*) from auctions WHERE a_title LIKE '%".$searchterm."%' OR category_id = '$catid'");

$cnt=mysql_fetch_assoc($countofrecs) ;

echo "Your search terms returned '{$cnt[0]}' results";

Re: Can't read csv file, page displays blank

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

You have syntax error in following line numbers of your code
line no. 3. fgetcsv($handle),500,"^")) // remove UNWANTED ) AFTER $HANDLE
line no. 8. write $row++ instead of row++
line no. 11. for ($c=0;$c<$num;$c++) // WRITE $c++ instead of c++

Re: How can I combine data from multiple rows into only one

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

SELECT sum(case when isnull(my_table.entry,0) > 0 
AND my_table.edate = convert(datetime,substring('2009-09-20',1,10)) 
THEN 1  else 0 END) AS no_entries_date1,
sum(case when isnull(my_table.entry,0) > 0 
AND my_table.edate = convert(datetime,substring('2009-09-21',1,10)) 
THEN 1  else 0 END) AS no_entries_date2,
sum(case when isnull(my_table.entry,0) > 0 
AND my_table.edate = convert(datetime,substring('2009-09-22',1,10)) 
THEN 1  else 0 END) AS no_entries_date3
FROM my_table

Re: SQL Query - combining two datasets

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

I hope following query will help you

SELECT case when t1.id is null then t2.id else t1.id end id
, case when t1.id is null then t2.value else t1.value end value
FROM db1.table1 t1 FULL OUTER JOIN db2.table2 t2 ON t1.ID=t2.ID

Re: SQL Query - combining two datasets

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

you may use left, right outer joins to control result as expected.

select t1.col1,t1.col2, t2.col1, t2.col2 
from db1.table1 t1 
left outer join db2.table2 t2 
on t1.CommonColName=t2.CommanColName

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

You may try follwing complex query. It may slow down performance but its due to the database design.

select id ,count(*) from(SELECT id,name1 name FROM table1 t
union
SELECT id,name2 name FROM table1 t
union
SELECT id,name3 name FROM table1 t
union
SELECT id,name4 name FROM table1 t) a
where name in ('roger','chill','dubs','lee')
group by id
having count(*)=4
order by id;

If you are searching for 2 name you must change 2 things
1) having count(*)=2 //number of names to be searched 1,2,3,4
2) where name in ('roger','chill') // actual names to be searched.

This query assumes that there is no duplication as follows
id, name1, name2, name3, name4
3, roger, mike, roger, lee

query will fail for above case, instead make sure to make name3 as null instead of repeating name.

rogerkore commented: Perfectly working. Thanks a lot +1

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Following query may not work for %mike%%roger%. Also if there is charlee in the database and if you search for %lee%, then charlee's record will also be listed.

select * from database.tables WHERE ( names like ('%roger%%mike%'));

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

JRM You are doing the same thing.
(name1 = 'roger' OR name1='dubs' OR name1='chill' OR name1='lee')
gives you same result as
name1 in ('roger','dubs','chill','lee')

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

It is a strange behaviour it is working here

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

it is really strange because as you go on removing condtions from query, you result no of rows increases normally.

Here it is working fine

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Even if you remove 4th condition you will be able to view the result, because one of the four names are there in your name1,name2 and name3. I do not know why you are not getting the result.

if sql looks longer, you will feel same when you try to find result using php code. better you continue with sql.

if you want to search two name then use following query

select * from something where 
name1 in ('roger','dubs')
and name2 in ('roger','dubs')
and name3 in ('roger','dubs')
and name4 in ('roger','dubs')

always keep all name1..4 columns in your query