using prepare

  • For security, I am trying to convert queries to $wpdb statements. I don’t know what to put in Array_A.

    $query = mysqli_query($link,"SELECT  MemberID, FirstName, LastName FROM AAUW_New_Members WHERE FirstName LIKE  '%$searchf%' AND Lastname LIKE '%$searchq%'") OR die("Could not  search!"); 
$sql = $wpdb->prepare("SELECT  MemberID, FirstName, LastName FROM AAUW_New_Members WHERE FirstName LIKE  '%$searchf%' AND Lastname LIKE '%$searchq%'"); 
$result = $wpdb->get_results($sql,ARRAY_A);
Viewing 6 replies - 1 through 6 (of 6 total)

  • We’re had this conversation before – using the $wpdb object is a good step in the right direction, but if you really wanted to put time into create a better environment for all the user attributes in your AAUW_New_Members table(s) then it would be much better to let the inherit framework do the all the heavy lifing you are doing by hand.

    array_a just defines how you want the result store in your object, which affects how you will display the resultset.
    Once you have your results in the array, you’ll need a foreach to output.
    You don’t need to “put anything in” array_a.

    Thread Starter hsysgrp

    (@hsysgrp)

    Sorry, I don’t know what “inherit framework” means. The result array consists of the values for MemberID, FirstName and LastName. $FirstName and $LastName are defined as $searchf and $searchq, syntactically, I don’t know how to represent MemberID.

    Moderator bcworkz

    (@bcworkz)

    I’m not sure either ?? My guess is “inherent framework”, meaning you could be using built-in AAUW functions to accomplish what you want. I’m unfamiliar with AAUW, so emphasis on “guess”. As a general rule, it’s better to use built-in functions to get data instead of writing your own SQL. Fallback to SQL when there is no appropriate built-in function, or it gives you noticeable performance improvement.

    Represent MemberID for what purpose where? You could var_dump( $result ); to see where if falls in the returned array if you’re trying to get at matched ID values.

    FYI, ARRAY_A is a pre-defined constant. It simply tells the function to return an associative array of data. So as Corrina said, you don’t put anything in it. You couldn’t if you wanted to (because it’s a constant), nor would there be any reason to. It wouldn’t accomplish anything since the function wouldn’t understand self-defined values.

    • This reply was modified 3 years, 6 months ago by bcworkz.
    Thread Starter hsysgrp

    (@hsysgrp)

    I am rewriting INSERT code that is successful to sanitize it.

    $sqlInsert .= "INSERT INTO AAUW_Members ( ID, Title, FirstName, LastName, Address1, City, State, Zip, HomePhone, CellPhone, Email, ";
//	$sqlInsert .= "College1, College2, College3, Birth_Day, Birth_Month, Mem_Type, Honorary, Joined_Local,Joined_Natl, Mailings, Positions_Held, Notes, ";
//	$sqlInsert .= "Referred, Retired, Employer, Occupation, Positions ) ";	
//	$sqlInsert .= " VALUES ( '$MemberID', '$Title', '$FirstName', '$LastName', '$Address1', '$City', '$State', '$Zip', '$HomePhone', '$CellPhone', ";  
//	$sqlInsert .= "'$Email', '$College1', '$College2', '$College3','$Birth_Day','$Birth_Month','$Mem_Type','$Honorary', ";
//	$sqlInsert .= "'$Joined_Local','$Joined_Natl','$Mailings','$Positions_Held', '$Notes', '$Referred', '$Retired', '$Employer', '$Occupation', ";
//	$sqlInsert .= "'$Positions' )";

    The replacement for only the first 8 fields:
    $wpdb->query(prepare( " INSERT INTO AAUW_Members(ID, Title, FirstName, LastName, Address1, City, State, Zip) VALUES ( %d, %s, %s, %s, %s, %s, %s, %s )", array($MemberID, $Title, $FirstName, $LastName, $Address1, $City, $State, $Zip ) ) );
    Error message says I have a null value.

    > I’m not sure either ?? My guess is “inherent framework”,

    My point is that the AAUW_Member Data is a tiny sub-set.
    You would be far far better off migrating all these Members and their metadata into wordpress tables. Then you can use the tools and *framework* provided to you by WordPress – like searching, sorting, security, Roles, Member editable Profiles, etc, etc.

    I’m not sure what the benefit is to keep all this data in custom tables, using custom sql to manage it all.

    > Error message says I have a null value.

    If the error msg says you have a null value – it’s bc you are either trying to insert a null value into a data column that does not allow nulls, or one of these values is empty:
    $MemberID, $Title, $FirstName, $LastName, $Address1, $City, $State, $Zip
    Use printr in your statement to print it screen.

    Thread Starter hsysgrp

    (@hsysgrp)

    echo shows $MemberID, $Title, $FirstName, $LastName, $Address1, $City, $State, $Zip are all populated. print_r ($sql) prints nothing. Tried $wpdb->AAUW_Members.
    Error says Error: Call to a member function prepare() on null in /home4/hsysgrpc/public_html/wp-content/themes/twentytwelve-child/custom-page_MoveNewMember.php:143. AAUW_Members is a custom table, no wp prefix.

    	$sql =  $wpdb->prepare( " INSERT INTO AAUW_Members (ID, Title, FirstName, LastName, Address1, City, State, Zip) VALUES ( %d, %s,  %s, %s, %s, %s, %s, %s ) " , $MemberID, $Title, $FirstName, $LastName, $Address1, $City, $State, $Zip ) ;
	$wpdb->query($sql);	
	print_r ($sql) ;
Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘using prepare’ is closed to new replies.