UPDATE MYSQL question

uMofobi

I am trying to construct an update query and its not working. Heres what im trying to do:

"UPDATE test_info
SET question_id = SELECT question_id FROM question
WHERE active=0
ORDER BY RAND()
LIMIT 50
WHERE test_id = '$test_id'";

Table 1: test_info
Fields: id, test_id, num, question_id, stud_answer

Table 2: question
Fields: question_id, type, question, answer, active

I am kinda new to this so please help. Is this way possible or is there a better way to do it. I'm trying to explain as clear and brief as possible. Thanks in advance.

sneakyimp

running an UPDATE query on an entire table by using a sub-select is a bit weird. I doubt your query works. Perhaps you could describe what you are doing instead of offering up some broken sql?

uMofobi

sneakyimp;10967208 wrote:
running an UPDATE query on an entire table by using a sub-select is a bit weird. I doubt your query works. Perhaps you could describe what you are doing instead of offering up some broken sql?

sneakyimp, thanks for the fast reply.

Let me try to clarify my problem:
basically what i need the query to do is update all the question_id field in table1 where test_id field = $variable. , by selecting 50 random active question_id's from table2. I believe i need to do a subquery, but im having trouble with the syntax. thanks again.

soluncenter249

hi,

i am new in php, i have just started, i have made a HTML form which i want to enter in database.
i have code - <form name="Form1" method="post" action="rail.php" enctype="text/plain" id="Form1" onsubmit="return ValidateForm1(this)">

rail.php code are as below : -

<?php
$mobile=$_POST['mobile'];
//for connection with my sql

$conn = mysql_connect('localhost','root','');

//connection confirmation with mysql

if(!$conn)
{
echo "Error".mysql_error();
}
else
{
$link = mysql_select_db('myfirstdatabase');
if(!$link)
{
echo "Error".mysql_error();
}
}

//echo $sql_qry= "INSERT INTO myfirstdatabase.offer (Sno, name, email, mobile, tripid) VALUES (NULL, '".$POST['name']."', '".$POST['email']."-".$POST['mobile']."-".$POST['tripid']."')";
//echo $sql_qry= "INSERT INTO myfirstdatabase.raildata (s.no, name, email, mobile, tripid) VALUES (NULL, '".$POST['name']."', '".$POST['email']."', '".$POST['mobile']."', '".$POST['tripid']."')";
echo $sql_qry= "INSERT INTO myfirstdatabase.raildata (sno, from, to, quota, trainname, ticket, class, date, return, pax, name, mobile, address, address2, email) VALUES (NULL, '".$POST['from']."', '".$POST['to']."', '".$POST['quota']."', '".$POST['trainname']."', '".$POST['ticket']."', '".$POST['class']."', '".$POST['date']."', '".$POST['return']."', '".$POST['pax']."', '".$POST['name']."', '".$POST['mobile']."', '".$POST['address']."', '".$POST['address2']."', '".$POST['email']."')";

//$output = mysql_query($sql_qry);

//if($output)
//{
//$success_url = "http://www.solutioncenter.co.in/smsconfirmation.php?mobile=$mobile";
//header('Location: '.$success_url);
// }

but when i run this programme i got error undefined index error

i alrady have check my form code i have written a name correctly as per rail.php codes, but i don know why its showing undefined index.

help would be appriciated

Thanks
Raj

sneakyimp

uMofobi;10967322 wrote:
basically what i need the query to do is update all the question_id field in table1 where test_id field = $variable. , by selecting 50 random active question_id's from table2. I believe i need to do a subquery, but im having trouble with the syntax. thanks again.

Your intent is getting clearer, but I don't know if an update is feasible. Perhaps you could DELETE the records in table 1 where test_id=$variable and then do an INSERT of random records? Something like this--suppose $variable was 22:

DELETE FROM table_1 WHERE test_id=2
INSERT INTO table_1 (test_id, field1, field2, fieldn) SELECT 2, field1, field2, fieldn FROM table_2 ORDER BY RANDOM() LIMIT 50;