Member Avatar for sparksguy

i am trying to make this code work with no success. Can anyone help me?
`
$to = $_POST["credits"];
$messaging = $_POST["message"];
$sub = $_POST["subject"];

@mysql_query("INSERT INTO mp_creditmail (Id, message, subject, read) ('select Id from oto_members ORDER BY RAND() Limit $to;', '$messaging', '$sub', no");`

i am trying to insert several instanses using random id's from a query that grabs them randomly limited the the amount of credits used. so if using 50 credits, will grab 50 random id's then insert the message, sub, into the database for each of the grabbed id's...

  • 3 Contributors
  • 3 Replies
  • 227 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by sparksguy

Dani AI

Generated

A single-statement solution is possible and usually cleaner than looping: use INSERT ... SELECT so the SELECT supplies the recipient Ids and the INSERT fills the constant columns (message, subject, read). That addresses the syntax problems pointed out (the original query treated the subquery as a string). was on the right track suggesting an INSERT...SELECT, and ’s final loop works too — looping is fine for small counts but is slower and causes more round-trips for larger batches.

Example SQL (constants shown inline for clarity — prefer prepared statements in real code):

INSERT INTO mp_creditmail (`Id`, `message`, `subject`, `read`)
SELECT Id, 'MSG_TEXT_HERE', 'SUBJECT_HERE', FALSE
FROM oto_members
ORDER BY RAND()
LIMIT 50;

A safer PHP approach with PDO (illustrative — some drivers do not allow binding LIMIT directly; cast integers explicitly if necessary):

$stmt = $pdo->prepare(
  "INSERT INTO mp_creditmail (`Id`,`message`,`subject`,`read`)
   SELECT Id, :msg, :subj, 0 FROM oto_members
   ORDER BY RAND() LIMIT :lim"
);
$stmt->bindValue(':msg', $messaging, PDO::PARAM_STR);
$stmt->bindValue(':subj', $sub, PDO::PARAM_STR);
$stmt->bindValue(':lim', (int)$to, PDO::PARAM_INT);
$stmt->execute();

Important notes not covered fully in the replies above:

  • Confirm table schema before inserting. If mp_creditmail.Id is an auto-increment primary key, inserting into it will fail or be wrong; the recipient column should be used instead (rename to recipient_id or omit auto-inc column in the INSERT).
  • Escape or rename reserved words like read (backticks shown) and validate/cast all POST data to avoid SQL injection and type errors. Use prepared statements rather than interpolating $_POST directly.
  • ORDER BY RAND() is convenient but expensive on large tables; for high-volume sampling consider indexed sampling strategies (random id ranges, reservoir sampling, or a precomputed random column) or do batched selects.

For small credit counts the loop approach that used is acceptable; for dozens/hundreds at once the INSERT...SELECT + prepared statements is generally simpler, more atomic, and far more efficient.

Recommended Answers

All 3 Replies

Member Avatar for Member #949455

i am trying to make this code work with no success. Can anyone help me?

What is the issue when you ran the code?

This is your query:

@mysql_query("INSERT INTO mp_creditmail (Id, message, subject, read) ('select Id from oto_members ORDER BY RAND() Limit $to;', '$messaging', '$sub', no");`

My question is very simple does your table mp_creditmail and oto_members have these columns:

    mp_creditmail (table)                  oto_members (table)

id | message | subject |read           id | message | subject |read

You didn't mention any errors nor what is your table structure so the only thing base on your query is to used (it's not tested):

INSERT INTO mp_creditmail (Id, message, subject, read) 
SELECT DISTINCT ID FROM oto_members ORDER BY RAND()
Member Avatar for |-|x

INSERT INTO mp_creditmail (Id, message, subject, read) ('select Id from oto_members ORDER BY RAND() Limit $to;', '$messaging', '$sub', no

If that is supposed to be a subquery, the syntax is wrong - it is currently trying to insert a string value 'select Id from ...'

You are also missing a closing bracket and the VALUES keyword, see MySQL Insert Syntax (also what is the "no" supposed to be at the end?)

In order to do this as a subquery it should be as follows

INSERT INTO mp_creditmail (Id, message, subject, read) VALUES ((select Id from oto_members ORDER BY RAND() Limit 1), '$messaging', '$sub', false)

AFAIK there is no way to duplicate multiple entries in a single statement like you are trying to do. You can include multiple records (VALUES sections) in a single INSERT statement, but the looping still needs to be done in PHP and not intrinsicly in SQL.

As Mitch said, please provide any specific error messages you are getting in order to get more direct advice.

Member Avatar for sparksguy

thanks guys, but i solved my problem by doing a clean random grab, and insert, then looping both queries equal to the amount of credits used.....

i appreciate your help though...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.