Jump to content

josephbupe

Members
  • Posts

    56
  • Joined

  • Last visited

Posts posted by josephbupe

  1. Ok, I got it working after some modifications:

    if(isset($_POST['upload'])){
            $familyname = trim($_POST['familyname']);
            $firstname = trim($_POST['firstname']);
            $othernames = trim($_POST['othernames']);
            $dateofbirth = trim($_POST['$dateofbirth']);
            $img = trim($_POST['imgurl']);
    
    $path=$path.$_FILES['file_upload']['name'];
       
    if(move_uploaded_file($_FILES['file_upload']['tmp_name'],$path))
    {
    echo " ".basename($_FILES['file_upload']['name'])." has been uploaded<br/>";
    echo '<img src="gallery/'.$_FILES['file_upload']['name'].'" width="80" height="100"/>';
    $img=$_FILES['file_upload']['name'];
        $query="insert into imgtables (familyname,firstname,othernames,dateofbirth,imgurl,date) values('$familyname','$firstname','$othernames',STR_TO_DATE('$dateofbirth','%d-%m-%y'),'$img',now())"; echo($familyname);
        if($sp->query($query)){
         echo "<br/>Inserted to DB also";   
        }else{
            echo "Error <br/>".$sp->error;       
        }
    
    }
    else
    {
    echo "There is an error,please retry or ckeck path";
    }
    }

    I am trying to convert to prepared statement.

     

    Regards.

     

    Joseph

  2. Hi,

     

    The following code was written by someone else. It allows me to upload images to a directory while saving image name in the mysql table.

     

    I also want the code to allow me save other data (surname, first name) along with the image name into the table, but my try is not working, only the images get uploaded.

     

    What am I missing here?

    if(isset($_POST['upload']))
    {
    
    $path=$path.$_FILES['file_upload']['name'];
    
    if(move_uploaded_file($_FILES['file_upload']['tmp_name'],$path))
    {
    echo " ".basename($_FILES['file_upload']['name'])." has been uploaded<br/>";
    echo '<img src="gallery/'.$_FILES['file_upload']['name'].'" width="48" height="48"/>';
    $img=$_FILES['file_upload']['name'];
    $query="insert into imgtables (fname,imgurl,date) values('$fname',STR_TO_DATE('$dateofbirth','%d-%m-%y'),'$img',now())";
    if($sp->query($query)){
    echo "<br/>Inserted to DB also"; 
    }else{
    echo "Error <br/>".$sp->error; 
    }
    }
    else
    {
    echo "There is an error,please retry or check path";
    }
    }
    
    ?>

    joseph

  3.  

    Put the id in a AgencyID column in the incident table. (as in your data model http://forums.phpfreaks.com/topic/291293-database-tables-relationship/?do=findComment&comment=1492162)

     

    You would only need a "junction table" if you have a many-to-many relationship. ie both these statements are true

    • An incident is handled by many agencies
    • An agency handles many incidents

     

     

     

    Hi Barand,

     

    Ok, Thanx.

     

    And If understand you well, I will create a foreign key AgencyID in the t_incidents from the t_users table (which also has a foreign key AgencyID from t_agencies).

     

    Is that so? Sorry, I just want to be clear on this one, noting also that I slightly departed from my previous table relationship between incidents and agencies after including users table.

     

    Joseph

  4. Hi,

     

    I want each new incident created into the t_incidents table by the user to be associated with the AgencyID, a foreign key in the t_users table.

     

    The problem is I do not know how this relationship will work and whether I will need a junction table.

     

    The two tables are:

    t_Users
    +----------+----------+------------+
    |UserID    | AgencyID |User name   |
    +----------+----------+------------+
    |1         | 1        |john        |
    +----------+----------+------------+
    |2         | 1        |andrew      |
    +----------+----------+------------+
    
    
    t_Agencies
    +----------+------------+
    |AgencyID  |agency name |
    +----------+------------+
    |1         |police      |
    +----------+------------+
    |2         |immigration |
    +----------+------------+

    I will appreciate your advice.

     

    Joseph

  5. $stmt->fetch() fetches a row of data from the result set and populates the variables that you used in the $stmt->bind_result($PersonID,$ImagePath,...) statement.

     

    the while(){} statement should be - 

    while ($stmt->fetch()) {

    and you would not use $row[0], $row[1], ... you would use $PersonID,$ImagePath,...

     

     

    Thank you Mac. It worked.

     

    ;D

  6. Hi,

    I am trying to migrate my project from mysql to mysqli but with little knowledge of the later.

    Among the noticeable errors is that the code is no longer fetching data from the database, and I am also getting UNDEFINED VARIABLE errors on the in the mysqli statement that should be binding results to variables.

    Here is the code if you can help:

    //set connection variables
    $host = "localhost";
    $username = "root";
    $password = "password";
    $db_name = "mydb"; //database name
    
    //connect to mysql server
    $mysqli = new mysqli($host, $username, $password, $db_name);
    
    //check if any connection error was encountered
    if(mysqli_connect_errno()) {
        echo "Error: Could not connect to database.";
        exit;
    }
    
    //Just counting ...required for pagination...
    $query = "SELECT COUNT(*) FROM t_persons";
    
    $result = mysqli_query($mysqli,$query) or die(mysqli_connect_errno());
    $num_rows = mysqli_fetch_row($result);
    
    
    $pages = new Paginator;
    $pages->items_total = $num_rows[0];
    $pages->mid_range = 9; // Number of pages to display. Must be odd and > 3
    $pages->paginate();
    
    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
        
        if ($stmt = $mysqli->prepare("SELECT p.PersonID,
                    p.ImagePath,
                    p.FamilyName,
                    p.FirstName,
                    p.OtherNames,
                    p.Gender,
                    p.CountryID,
                    p.StatusID,
                    i.IncidentDate,
                    i.IncidentCountryID,
                    i.AgencyID,
                    i.KeywordID
     
    FROM t_incidents i
    INNER JOIN
      t_incident_persons ip ON ip.IncidentID = i.IncidentID
    INNER JOIN
      t_persons p ON ip.PersonID = p.PersonID    
    
    WHERE p.PersonID>0" . $likes . " ORDER BY p.PersonID DESC $pages->limit")) {    
        
        /* Execute the prepared Statement */
        $stmt->execute();
        
        /* Bind results to variables */
        $stmt->bind_result($PersonID,$ImagePath,$FamilyName,$FirstName,$OtherNames,$Gender,$CountryID,$StatusID,$IncidentDate,$IncidentCountryID,$AgencyID,$KeywordID);
        
        /* fetch values */
        while ($rows = $stmt->fetch()) {
         // display records in a table
    
    //    $PersonID=$row[0];
    ?>
        <div class="thumbnail">
    <a href=details.php?PersonID=<?php echo $PersonID ?> target=gallery><img src="./Persons_Images/<?php echo $row[1]; ?>" width="100" height="130" alt="" /></a><br>
    <a href="details.php?PersonID=<?php echo $PersonID; ?>"> <?php echo $row[2]; ?> <?php echo $row[3]; ?></a>
    </div>
    
      <?php }?>
    
    <?php
        }
    
        /* Close the statement */
        $stmt->close();
    
    
    /* close our connection */
    $mysqli->close();
    ?>               
    
    

    I will appreciate.

     

    Joseph

     

     

  7. Hi,

     

    First, I wish to apologize for taking too long to acknowledge your responses to my earlier question above.

     

    The suggested code did not return records between specified date range. Here is the query echo:

    SELECT * FROM t_persons WHERE PersonID>0 AND IncidentDate BETWEEN '' AND '' ORDER BY p.PersonID DESC LIMIT 0,27

    For clarification purpose, I want to know what these variables refer to:

     

    1.  $start

    2.  $end

     

    I assume that:

     

    start_date is the name of the field to input the first date

    end_date is the name of the field to input the second date

     

    I appreciate your further assistance.

     

    Joseph

  8. Hi,

     

    How can I select multiple columns from two tables and run a search through multiple fields?

     

    My tables are:

     

    t_persons (holds information about persons)

    t_incidents (holds foreign keys from other tables including t_persons table)

     

    What I want is to pull some columns from the two tables above and run a search with a LIKE criteria, something like below. The code originally worked well with only one table, but for two tables it generate errors:

    $query = "SELECT
    p.PersonID
    ,p.ImagePath
    ,p.FamilyName
    ,p.FirstName
    ,p.OtherNames
    ,p.Gender
    ,p.CountryID
    ,i.IncidentDate
    ,i.KeywordID
    ,i.IncidentCountryID
    ,i.StatusID
    
    FROM t_incidents AS i
        LEFT JOIN t_persons AS p ON i.PersonID = p.PersonID
    WHERE
        FamilyName LIKE '%" . $likes . "%' AND
        FirstName LIKE '%" . $likes . "%' AND
        OtherNames LIKE '%" . $likes . "%' AND
        Gender LIKE '%" . $likes . "%' AND
        IncidentDate LIKE '%" . $likes . "%' AND
        KeywordID LIKE '%" . $likes . "%' AND
        IncidentCountryID LIKE '%" . $likes . "%' AND
        StatusID LIKE '%" . $likes . "%'
    ORDER BY PersonID DESC $pages->limit";

    Errors are:

    Column 'IncidentDate' in where clause is ambiguous
    Column 'KeywordID' in where clause is ambiguous
    Column 'IncidentCountryID' in where clause is ambiguous
    Column 'StatusID' in where clause is ambiguous
    

    These columns are foreign keys on t_incidents table. I have also attached the table relationship diagram if it helps.

     

    I will appreciate any better way to do this.

     

    Thanx.

     

    Joseph

    post-134077-0-99718400-1412145072_thumb.png

  9. Hi,

    I am re-visiting my database table relationship for criminal incidents.

    Six tables are involved plus a junction table to make it seven tables in total.
    I need someone to review this relationship and advise whether or not it is correctly set up.

    The relationships are as follows:

    1. A person can belong to more than one incident:

    t_persons (one-to-many) t)_incidents_persons


    2. A person can only have one nationality by birth

    t_persons (one-to-one) t_countries

    3. An agency can have more than one incident

    t_agencies (one-to-many) t_incidents


    4. A status (e.g. closed incident) can belong to more than one incident

    t_status (one-to-many_ t_incidents

    5. A keyword (i.e. offence type eg theft)

    t_offencekeywords (one-to-many) t_incidents

  10. Hi,

     

    I am trying to align sections of my CSS layout side-by-side, but I am having problems with it.

     

    I expect the left sidebar (#aside) with a 30% width to meet with the content section (#content) of the width of 70%. But, the changes I make are not translating as expected.

     

    See attached screenshort of what is happening and the CSS code bellow:

    #container
    {
    	margin: 0 auto;
    	width: 800px;
    	background: #fff;
    }
    
    #header
    {
    	background: #5a83c3;
    	padding: 20px;
    }
    
    #header h1 { margin: 0; }
    
    #navigation
    {
    	float: left;
    	width: 100%;
    	background: #d1dceb;
    		border-top: 1px solid #fff;
    }
    
    #navigation ul
    {
    	margin: 0;
    	padding: 0;
    }
    
    #navigation ul li
    {
    	list-style-type: none;
    	display: inline;
    }
    
    #navigation li a
    {
    	display: block;
    	float: left;
    	padding: 5px 10px;
    	color: #fff;
    	text-decoration: none;
    	border-right: 1px solid #fff;
    }
    
    #navigation li a:hover { background: #383; }
    
    #content-container
    {
    	float: right;
    	width: 100%;
    	background: #FFF url(layout-two-liquid-background.gif) repeat-y 68% 0;
    }
    
    #content
    {
    	clear: right;
    	float: right;
    	width: 70%;
    	padding: 10px 0;
    	margin: 0 0 0 4%;
    	display: inline;
    	background: #dfe6ef;
    	border-top: 1px solid #fff;
    	border-left: 1px solid #fff;
    	border-bottom: 1px solid #fff;
    }
    
    #content h2 { margin: 0; }
    
    #aside
    {
    	float: left;
    	width: 20%;
    	padding: 10px 0;
    	margin: 0 3% 0 0;
    	display: inline;
    	background: #d1dceb;
    	border-top: 1px solid #fff;
    }
    
    #aside h3 { margin: 0; }
    
    #footer
    {
    	clear: right;
    	background: #d1dceb;
    	text-align: right;
    	padding: 20px;
    	width: 68%;
    	float: right;
    	overflow: hidden;
    }
    #footer p.left {
    float: left;
    text-align: left;
    margin-left: 5px;
    }
    
    #footer p.right {
    float: right;
    text-align: right;
    margin-right: 5px;
    }
    

    post-134077-0-09004100-1410780602_thumb.png

  11. Hi,

     

    I am developing a web application which will be comprised of three columns: (1) Search form, (2) Image gallery, (3) Image details. The site is going to be more like html framesets, but research reveals that framesets are not favourable due to many reasons. I however found an alternative that I want to use instead of html framesets http://www.ironspider.ca/frames/smartframes.htm

     

    I thought it would be easy for me to add rows which should serve as a page header, but I was wrong. I tried adding

     

     

    document.write('<frameset rows="17%,*" frameborder="no">');

     

    to this part of the code, but row apeared in a wrong place at the bottom:

    // Writes frameset
    
    document.write('<frameset rows="17%,*" frameborder="no">');
    document.write('<frameset cols="25%,75%" frameborder="no">');
    document.write('<frame src="' + menuURL + '" name="menu">');
    document.write('<frame src="' + contentURL + '" name="content" marginheight="15" marginwidth="50" scrolling="yes">');
    // -->
    

    The original script for the index.html page is as follows:

    <!--
    /* SmartFrames - Author: Robert Darrell - http://www.ironspider.ca/ */
    // Defines variables
    // REPLACE menu.htm WITH YOUR MENU PAGE
    // REPLACE page1.htm WITH YOUR INITIAL CONTENT PAGE
    menuURL = "menu.htm";
    contentURL = "page1.htm";
    reloadURL = parent.document.URL;
    orphanURL = reloadURL.substring(reloadURL.indexOf('?')+1, reloadURL.indexOf('&'));
    frameName = reloadURL.substring(reloadURL.indexOf('&')+1, reloadURL.length);
    
    // Reassigns variable according to frame name passed by orphan
    if (frameName == "menu") {
    menuURL = orphanURL; 
    } else if (frameName == "content") {
    contentURL = orphanURL;
    }
    
    // Writes frameset
    document.write('<frameset cols="25%,75%" frameborder="no">');
    document.write('<frame src="' + menuURL + '" name="menu">');
    document.write('<frame src="' + contentURL + '" name="content" marginheight="15" marginwidth="50" scrolling="yes">');
    // -->
    </script>
    
    <!-- Use the noframes section to help search engines index your site -->
    <noframes>THIS WEBSITE IS ABOUT...<br><br>
    <a href="menu.htm">Site Menu</a>
    </noframes>
    
    </frameset>
    

    I hope someone will help.

     

    Regards.

     

    joseph

     

  12. Hi,

    I want to restructure my database tables so that I can have one table (t_incidents) to hold foreign keys instead of holding foreign keys in the table "t_persons" because one person can commit more than one offense.

    However, I need to know how the join should be implemented with MySQLi prepared statement.

    I need some one to review the following statement for me and advise:

    if ($stmt = $mysqli->prepare("Select t_persons.PersonID    
                                                                        
            ,t_persons.FamilyName
            ,t_persons.FirstName
            ,t_persons.OtherNames
            ,t_persons.Gender
            ,t_persons.CountryID
            ,t_persons.ImagePath    
    
            ,t_incidents.IncidentID
            ,t_incidents.Incident
            ,t_incidents.IncidentDate
            ,t_incidents.PersonID
            ,t_incidents.CountryID
            ,t_incidents.OffenceKeywordID
            ,t_incidents.StatusID
            ,t_incidents.AgencyID
    
            ,t_status.StatusID
            ,t_status.Status
    
            ,t_offencekeyword.KeywordID
            ,t_offencekeyword.Keyword
    
            ,t_countries.CountryID
            ,t_countries.Country
    
            ,t_agencies.AgencyID
            ,t_agencies.Agency
    
    From t_persons
        Inner Join t_incidents On t_persons.PersonID = t_incidents.PersonID
        Inner Join t_incidents On t_countries.CountryID = t_incidents.CountryID
        Inner Join t_incidents On t_status.StatusID = t_incidents.StatusID
        Inner Join t_incidents On t_offenceskeyword.KeywordID = t_incidents.KeywordID
        Inner Join t_incidents On t_agencies.AgencyID = t_incidents.AgencyID
    
    Where t_persons.PersonID = '$PersonID'")) {
    

    Regards.

    josephbupe

  13. Ok, now I need help to merge the following two pieces of code into a single block for my search criteria. The two codes are both IF statements and I am lost on how to make them work as a whole.

     

    Actually, I have been using the first, but the second has been suggested to me:

    $criteria = array('FamilyName', 'FirstName', 'OtherNames', 'NRCNo', 'PassportNo', 'Gender', 'IncidenceCountryID', 'Status', 'OffenceKeyword', 'AgencyID', 'CountryID', 'IncidenceCountryID' );
        $likes = "";
        $url_criteria = '';
        foreach ( $criteria AS $criterion ) {
                if ( ! empty($_POST[$criterion]) ) {
                        $value = ($_POST[$criterion]);
                        $likes .= " AND `$criterion` LIKE '%$value%'";
                        $url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]);
                } elseif ( ! empty($_GET[$criterion]) ) {
                        $value = mysql_real_escape_string($_GET[$criterion]);
                        $likes .= " AND `$criterion` LIKE '%$value%'";
                        $url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]);
                } //var_dump($likes);
        }
        $sql = "SELECT * FROM t_persons WHERE PersonID>0" . $likes . " ORDER BY PersonID DESC";
    

    And the date range criteria code:

    if (!empty($_REQUEST['start_date']) && !empty($_REQUEST['end_date'])) {
        $start = mysqli_real_escape_string($con, $_REQUEST['start_date']);
        $end = mysqli_real_escape_string($con, $_REQUEST['end_date']);
        $likes .= " AND IncidentDate BETWEEN '$start' AND '$end'";
        $url_criteria .= '&start_date='.htmlentities($_REQUEST['start_date']).'&end_date='.htmlentities($_REQUEST['end_date']);
    }
    
  14. Hi,

     

    I can I include a date range criteria to query with in the following code? The date field in the table (t_persons) is IncidentDate.

    $criteria = array('FamilyName', 'FirstName', 'OtherNames', 'NRCNo', 'PassportNo', 'Gender', 'IncidenceCountryID', 'Status', 'OffenceKeyword', 'AgencyID', 'CountryID', 'IncidenceCountryID' );
        $likes = "";
        $url_criteria = '';
        foreach ( $criteria AS $criterion ) {
                if ( ! empty($_POST[$criterion]) ) {
                        $value = ($_POST[$criterion]);
                        $likes .= " AND `$criterion` LIKE '%$value%'";
                        $url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]);
                } elseif ( ! empty($_GET[$criterion]) ) {
                        $value = mysql_real_escape_string($_GET[$criterion]);
                        $likes .= " AND `$criterion` LIKE '%$value%'";
                        $url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]);
                } //var_dump($likes);
        }
        $sql = "SELECT * FROM t_persons WHERE PersonID>0" . $likes . " ORDER BY PersonID DESC";

    Kind regards.

     

  15. Hi all,

     

    I wish I could use the mysqli/ PDO to accomplish what I am trying to do right away. I do not know either how I can intergrate the "=" criteria with the "LIKE" in the following piece of code.

     

    The code is for searching with multiple criterion. The search with this cat_id is okey expect that I want its criteria not to be part of the "LIKE" criteria; instead it should be "=" so that I can get the exact match in the search, since cat_id is saved as a foreign key in the child table. May be some form of switch but how?

    $criteria = array('ctitle', 'csubject', 'creference', 'cat_id', 'cmaterial', 'ctechnic', 'cartist', 'csource', 'stolen');
    $likes = "";
    $url_criteria = '';
    foreach ( $criteria AS $criterion ) {
            if ( ! empty($_POST[$criterion]) ) {
                    $value = ($_POST[$criterion]);
                    $likes .= " AND `$criterion` LIKE '%$value%'";
                    $url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]);
            } elseif ( ! empty($_GET[$criterion]) ) {
                    $value = mysql_real_escape_string($_GET[$criterion]);
                    $likes .= " AND `$criterion` LIKE '%$value%'";
                    $url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]);
            } //var_dump($likes);
    }
    $sql = "SELECT * FROM collections WHERE c_id>0" . $likes . " ORDER BY c_id ASC";

    Your help will be appreciated.

     

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.