check data type and value

[mtvaran]

hi guys,  in my form i need to insert an ID which is 5 digits number only. can anyone please tell me how can i check this before i insert? and also i need to check existing ID in my database.

[Adam]

You can use regex to validate the format:

 

if (!preg_match('/^[0-9]{5}$/', $id))
{
    // invalid
}

 

Not sure what you mean by 'checking existing ID', but that should just be a simple query.

[mtvaran]

first of all thank you so much for the help. i meant the existing value was if i already entered the same ID then i cant enter again. if you can help me pls... thanks

[Adam]

Hmm well as I said, just requires a basic query:

 

if (!preg_match('/^[0-9]{5}$/', $id))
{
    // invalid format
}

$sql = "SELECT id FROM table_name WHERE id =" . $id;
$query = mysql_query($sql) or trigger_error('MySQL error: ' . mysql_error());

if (mysql_num_rows($query) > 0)
{
    // already taken
}

 

You'll need to handle the errors of course.

[mtvaran]

Adam, i cudn't correcr errors. this is my code..... can you pls have a look....

 

<?php

$con = mysql_connect("localhost","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db("uni", $con);

 

$sql="INSERT INTO Student (StudentID,StudentName)

VALUES ('$_POST[sid]','$_POST[sname]')";

 

if(!mysql_query($sql,$con))

  {

  die('Error: ' . mysql_error());

  }

 

if (!preg_match('/^[0-9]{7}$/', $_POST['sid']))

{

  echo"existing id ......... "; // invalid

}

 

$sql = "SELECT StudentID FROM student WHERE StudentID =" . $sid;

$query = mysql_query($sql); or trigger_error('MySQL error: ' . mysql_error());

 

if (mysql_num_rows($query) > 0)

{

    echo"id  already taken......";// already taken

}

 

mysql_close($con)

?>

 

[Anti-Moronic]

Seems to me your INSERT is coming before your SELECT which will not work if the ID is a unique column. You need to do the SELECT first, then only INSERT if you don't find an occupied row.

 

..and my god VALIDATE all user data before you blindly insert into a database!!

 

[Adam]

Adding to what Anti-Moronic posted, you also need to handle the errors. Echoing out an error message will simply just print the message and the script will move on to the insert (once it's been moved) - you need to handle the errors.

 

There's about a million ways to handle errors, so here's a very simple example using exceptions (a "try..catch" block):

 

try
{
    if (!preg_match('/^[0-9]{7}$/', $_POST['sid']))
    {
        throw new Exception('Invalid ID format');
    }

    $sql   = "SELECT StudentID FROM student WHERE StudentID =" . $_POST['sid'];
    $query = mysql_query($sql) or trigger_error('MySQL error: ' . mysql_error());

    if (mysql_num_rows($query) > 0)
    {
        throw new Exception('ID already taken');
    }

    $sql="
        INSERT INTO Student (StudentID, StudentName)
        VALUES (
            '" . $_POST['sid'] . "'
          , '" . mysql_real_escape_string($_POST['sname']) . "'
        )
    ";

    $insert = mysql_query($sql) or trigger_error('MySQL error: ' . mysql_error());

    echo 'Success';
}
catch (Exception $e)
{
    echo $e->getMessage();
}

 

Within the try block, if an  exception is thrown the catch block will handle it and nothing else within the try block will be executed. This is useful for situations like you have, where you don't want to continue execution of a certain part of the script if there's an error.

 

I've left the MySQL errors using trigger_error() still though, as they're not strictly application errors, but fatal errors that should be fixed during development; on a production website you'd hide PHP errors so the end-user never seems them using: ini_set('display_errors', 0);

 

Hope this helps.

[rwwd]

even lighter regex:

preg_match('/^[\d]{5}$/', $id)

I prefer using this as it' supposedly lighter on overhead, but I just think as it is another option to entertain!

 

Rw

[Adam]

Fair enough, I hadn't read anything about performance being faster with it. Although using \d you wouldn't actually need the block brackets:

 

preg_match('/^\d{5}$/', $id)

 

 

[rwwd]

^^ we are talking about pS/uS of difference here, so nothing major.  Yeah, dodgy copy and pasting wrt the code I posted - I just thought I would offer another perspective on the same thing.

 

Rw

[mtvaran]

wow... ur genius Adam, now it works. thank you so mch for ur help. hope you will be bearing with me for helping in future. thanks again.

thank you my other guys for ur kind reply.

[mtvaran]

pls any1 tell me this simple code for preg_match .............      "only allowing  a-z & A-Z in text field" not nubmer things

[Adam]

Here you go:

 

/^[a-zA-Z]+$/

 

That will require at least 1 character a-z A-Z for a successful match.

[mtvaran]

thanks alot.

[mtvaran]

Hi Adam, i have posted this before but i cudn't get the proper answer jet. if you can pls hava a look. i have already displayed data from database. one is drop-down list another one multi select list, now i need to select one data from drop-down list and one or more data from multi select list then send to another table. hope i have made this clear understand for you. so cud you plss help me if you can ...

 

 

// coding for displaying drop-down list
<?php

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("uni", $con);
  
  
$result  = mysql_query("SELECT * FROM student");

echo "<select>";

while($row = mysql_fetch_array($result))
{

echo "<option>";
echo $row['StudentID'];
echo " - ";
echo $row['StudentName'];
  echo "</option>";
} 
echo "</select>";
mysql_close($con);

?>

// this is coding for multi select list

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("uni", $con);
  
  
$result  = mysql_query("SELECT * FROM course");

echo "<select multiple='multiple' size = '10'>";

while($row = mysql_fetch_array($result))
{
echo "<option value=''>";
echo $row['CourseID'];
echo "-";
echo $row['CourseName'];
  echo "</option>";
   
} 
echo "</select>";
mysql_close($con);

?>

 

 

MOD EDIT:

 . . . 

[/nobc] tags added . . .

[Adam]

Sorry I'm not sure I follow 100%. The way you've posted these they look like two separate files, yeah? You want to select 1 student and multiple courses, from the same page, then enter the selected data into another table? If so, what table? What's the table structure like?

[mtvaran]

well let me explain, i have two table in my database which are student (st_id, st_name)& course(course_id, course_name). i have displayed the field st_id from student (drop-down list) & course_id from course (multi select list). now i need to insert into another table called take which contain the feild st_id and course_id. so i have to select one st_id and one or more course_id then submit. hope you get my point and not too difficult to do this for you.

[Adam]

This should handle updating the database:

 

if (!empty($_POST['studentID']) && !empty($_POST['courseIDs']))
{
    $student = mysql_real_escape_string($_POST['studentID']);
    $sql = "INSERT INTO take (st_id, course_id) VALUES";

    foreach ($_POST['courseIDs'] as $key => $course)
    {
        $sql .= ($key != 0) ? ", " : " ";
        $sql .= "('" . $student . "', '" . mysql_real_escape_string($course) . "')";
    }

    $query = mysql_query($sql) or trigger_error('MySQL error: ' . mysql_error());

    if (mysql_affected_rows($query) > 0)
    {
        echo 'Success';
    }
}

 

Very rushed as I'm leaving soon. Just remember to add the correct input names ('studentID', 'courseIDs' in my code) to your form. Also for multiple select fields you need to add "[]" to the end of the name (e.g. name="courseIDs[]"), in order to submit the data as an array; otherwise only the last selected option will be sent.

[mtvaran]

thanks so much Adam for helping me alot today, i really appreciate.  i will try to run this code if i can't i will ask you later.

takecare.

[mtvaran]

Hi Adam, i wrote the coding as you said but when i run it  doesn't say any error massage only says Apache server stop working! so cud you pls check the code if i have made any mistake?

 

<?php

 

$con = mysql_connect("localhost","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

  mysql_select_db("uni", $con);

 

$result  = mysql_query("SELECT * FROM course");

 

echo "<select name ='"cid[]"' multiple='multiple' size = '10'>";

 

while($row = mysql_fetch_array($result))

{

echo "<option value=''>";

echo $row['CourseID'];

 

  echo "</option>";

 

}

echo "</select>";

mysql_close($con);

 

?>

--------------------------------------------------------------

 

<?php

 

$con = mysql_connect("localhost","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

  mysql_select_db("uni", $con);

 

$result  = mysql_query("SELECT * FROM student");

 

echo "<select name = '"sid[]"'>";

 

while($row = mysql_fetch_array($result))

{

 

echo "<option value=''>";

echo $row['StudentID'];

 

  echo "</option>";

 

}

echo "</select>";

mysql_close($con);

 

?>

 

------------------------------------------------

<?php

$con = mysql_connect("localhost","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

 

  mysql_select_db("uni", $con);

if (!empty($_POST['sid']) && !empty($_POST['cid']))

{

    $student = mysql_real_escape_string($_POST['sid']);

    $sql = "INSERT INTO take (StudentID, CourseID) VALUES";

 

    foreach ($_POST['cid'] as $key => $course)

    {

        $sql .= ($key != 0) ? ", " : " ";

        $sql .= "('" . $student . "', '" . mysql_real_escape_string($course) . "')";

    }

 

    $query = mysql_query($sql) or trigger_error('MySQL error: ' . mysql_error());

 

    if (mysql_affected_rows($query) > 0)

    {

        echo 'Success';

    }

}

 

mysql_close();

?>

[Adam]

Okay, first of all you don't need to connect and close the connection for each query. You'd be better off taking the connection code and storing within a separate file, so that at the top of any code requiring a connection you can just include it.

 

global "connection.php" file:

<?php

$con = mysql_connect("localhost","root","");
mysql_select_db("uni", $con) or trigger_error('MySQL error: ' . mysql_error());

?>

 

code for this file:

<?php

// include connection
require_once 'path/to/connection.php';

$result  = mysql_query("SELECT * FROM course") or trigger_error('MySQL error: ' . mysql_error());

// notice below I've used single quotes for strings as it's easier to write HTML
echo '<select name ="cid[]" multiple="multiple" size="10">';

while($row = mysql_fetch_array($result))
{
    // previously you hadn't set the value of the option
    echo '<option value="' . $row['CourseID'] . '">' . $row['CourseName'] . '</option>';     
}

echo '</select>';

// ----------------

$result  = mysql_query("SELECT * FROM student") or trigger_error('MySQL error: ' . mysql_error());

// you don't want to pass the student ID as an array so remove the block brackets
echo '<select name="sid">';

while($row = mysql_fetch_array($result))
{
    // same problem as before
    echo '<option value="' . $row['StudentID'] . '">' . $row['StudentName'] . '</option>';   
}

echo '</select>';

// ----------------

if (!empty($_POST['sid']) && !empty($_POST['cid']))
{
    $student = mysql_real_escape_string($_POST['sid']);
    $sql = "INSERT INTO take (StudentID, CourseID) VALUES";

    foreach ($_POST['cid'] as $key => $course)
    {
        $sql .= ($key != 0) ? ", " : " ";
        $sql .= "('" . $student . "', '" . mysql_real_escape_string($course) . "')";
    }

    $query = mysql_query($sql) or trigger_error('MySQL error: ' . mysql_error());

    if (mysql_affected_rows($query) > 0)
    {
        echo mysql_affected_rows($query) . ' rows added.';
    }
}

?>

 

Tidied up the code and fixed a couple of bugs which I've put comments next to. Unfortunately though it sounds like you're having a server problem, as opposed to the code - although I'm not familiar with that error message. Could you send a screen shot of the error to make things easier?

 

Edit:

 

Of course you'll need a way to submit the form, don't forget.

[mtvaran]

thanks for the coding. i have done as u said and now it works without any error message but its not inserting the selected data into the database (take table) im not sure why it is! would you like me to send any of my coding part?

[mtvaran]

could  anyone pls check this coding? i need to display the data from database into the text field  for editing then submit to the existing database.

 

 

mysql_select_db("uni", $con);

 

$sql=mysql_query("select * from student");

 

$res=0;

 

while($row=mysql_fetch_array($sql))

{

if($row['StudentID']==$_POST["sid"])

 

{

?>

<form id="form2" name="form2" method="post" action="update.php">

 

  <h2><p><?php echo "StudentID :" ?> <input type="text" name="sid" id="sid" value="<?php echo"".$row['StudentID'] ?>" />

 

  <p><?php echo "StudentName :" ?> <input type="text" name="stname" id="stname" value="<?php echo"".$row['StudentName'] ?>" /> </p>

 

 

<?php

  $res=1;

}

 

}?> <h2> <?php

 

 

if($res==0)

{

echo "Please enter the Correct ID. ";

}

 

 

 

 

 

update.php

 

mysql_select_db("uni", $con);

 

mysql_query("UPDATE student SET StudentID = .$row['StudentID']

WHERE student.StudentID = '.$row['StudentID']'");

 

mysql_query("UPDATE student SET StudentName = .$row['StudentName']

WHERE student.StudentID = '.$row['StudentID']'");

 

echo "Successfully Edited";

 

//submit button

[Pikachu2000]

When posting code, please enclose it in the forum's

 . . . 

BBCode tags. You'll find that more people are actually willing to look at it when it's properly formatted and highlighted.

[mtvaran]

thank you . i will do next time

 

[attachment deleted by admin]